I have a query that runs quite nicely in SQL but I have decided to use
it in Access with ODBCs  could someone please convert it to Access
format for me.  Here is the query:

SELECT     dbo.Lot_Space.Location_Cd AS Location,
dbo.[Section].Sectn_Cod_Desc AS Section, dbo.Lot_Space.Lot_Row_Nbr_Num
AS [Row Number],
                      dbo.Lot_Space.Lot_Row_Nbr_Alpha AS [Row Alpha],
dbo.Lot_Space.Lot_Lot_Nbr_Num AS [Lot Number],
                      dbo.Lot_Space.Lot_Lot_Nbr_Alpha AS [Lot Alpha],
dbo.Lot_Space.Lot_Space_Nbr AS [Space Number],
                      dbo.Lot_Space.Lot_Space_Nbr_2 AS [Space Alpha],
dbo.Lot_Space.Lot_Depth AS Depth, dbo.Lot_Space.Lot_Sell_Unit_ID AS
LSU,
                      dbo.Lot_Space.Lot_Space_ID AS LSID,
                      'PreDeveloped' = Case
dbo.Lot_Sell_Unit.PreDeveloped  when 1 then 'PreDeveloped' when 0 then
'Developed' END,
                      dbo.Lot_Space.Location AS Description, 
                      'Lot Status' = case dbo.Lot_Space_Status.Descr
when 'sold' then 'Sold' when 'not for sale' then 'Unsaleable'
                       when 'not assigned' then 'Sold Unassigned' when
'reserved' then 'Sold'when 'occupied' then 'Sold'
                       when 'available' then 'Available' END
FROM       dbo.Lot_Sell_Unit INNER JOIN
           dbo.Lot_Space ON dbo.Lot_Sell_Unit.Lot_Sell_Unit_ID =
dbo.Lot_Space.Lot_Sell_Unit_ID INNER JOIN
           dbo.[Section] ON dbo.Lot_Space.Location_Cd =
dbo.[Section].Location_Cd AND dbo.Lot_Space.Section_Cd =
dbo.[Section].Section_Cd INNER JOIN
           dbo.Lot_Space_Status ON dbo.Lot_Space.Lot_Space_Status_Cd =
dbo.Lot_Space_Status.Lot_Space_Status_Cd
WHERE     (dbo.Lot_Space.Location_Cd = '0104')

Access still uses SQL (the Structured Query Language) for queries. 
You probably mean SQL server though.  Depends completely on how you
imported it, but try just removing 'dbo.' (the object owners):


SELECT     Lot_Space.Location_Cd AS Location,
[Section].Sectn_Cod_Desc AS Section, Lot_Space.Lot_Row_Nbr_Num
AS [Row Number],
                      Lot_Space.Lot_Row_Nbr_Alpha AS [Row Alpha],
Lot_Space.Lot_Lot_Nbr_Num AS [Lot Number],
                      Lot_Space.Lot_Lot_Nbr_Alpha AS [Lot Alpha],
Lot_Space.Lot_Space_Nbr AS [Space Number],
                      Lot_Space.Lot_Space_Nbr_2 AS [Space Alpha],
Lot_Space.Lot_Depth AS Depth, Lot_Space.Lot_Sell_Unit_ID AS
LSU,
                      Lot_Space.Lot_Space_ID AS LSID,
                      'PreDeveloped' = Case
Lot_Sell_Unit.PreDeveloped  when 1 then 'PreDeveloped' when 0 then
'Developed' END,
                      Lot_Space.Location AS Description, 
                      'Lot Status' = case Lot_Space_Status.Descr
when 'sold' then 'Sold' when 'not for sale' then 'Unsaleable'
                       when 'not assigned' then 'Sold Unassigned' when
'reserved' then 'Sold'when 'occupied' then 'Sold'
                       when 'available' then 'Available' END
FROM       Lot_Sell_Unit INNER JOIN
           Lot_Space ON Lot_Sell_Unit.Lot_Sell_Unit_ID =
Lot_Space.Lot_Sell_Unit_ID INNER JOIN
           [Section] ON Lot_Space.Location_Cd =
[Section].Location_Cd AND Lot_Space.Section_Cd =
[Section].Section_Cd INNER JOIN
           Lot_Space_Status ON Lot_Space.Lot_Space_Status_Cd =
Lot_Space_Status.Lot_Space_Status_Cd
WHERE     (Lot_Space.Location_Cd = '0104')
 



Or replacing with dbo_ (as is the case with Linked tables):



SELECT     dbo_Lot_Space.Location_Cd AS Location,
dbo_[Section].Sectn_Cod_Desc AS Section, dbo_Lot_Space.Lot_Row_Nbr_Num
AS [Row Number],
                      dbo_Lot_Space.Lot_Row_Nbr_Alpha AS [Row Alpha],
dbo_Lot_Space.Lot_Lot_Nbr_Num AS [Lot Number],
                      dbo_Lot_Space.Lot_Lot_Nbr_Alpha AS [Lot Alpha],
dbo_Lot_Space.Lot_Space_Nbr AS [Space Number],
                      dbo_Lot_Space.Lot_Space_Nbr_2 AS [Space Alpha],
dbo_Lot_Space.Lot_Depth AS Depth, dbo_Lot_Space.Lot_Sell_Unit_ID AS
LSU,
                      dbo_Lot_Space.Lot_Space_ID AS LSID,
                      'PreDeveloped' = Case
dbo_Lot_Sell_Unit.PreDeveloped  when 1 then 'PreDeveloped' when 0 then
'Developed' END,
                      dbo_Lot_Space.Location AS Description, 
                      'Lot Status' = case dbo_Lot_Space_Status.Descr
when 'sold' then 'Sold' when 'not for sale' then 'Unsaleable'
                       when 'not assigned' then 'Sold Unassigned' when
'reserved' then 'Sold'when 'occupied' then 'Sold'
                       when 'available' then 'Available' END
FROM       dbo_Lot_Sell_Unit INNER JOIN
           dbo_Lot_Space ON dbo_Lot_Sell_Unit.Lot_Sell_Unit_ID =
dbo_Lot_Space.Lot_Sell_Unit_ID INNER JOIN
           dbo_[Section] ON dbo_Lot_Space.Location_Cd =
dbo_[Section].Location_Cd AND dbo_Lot_Space.Section_Cd =
dbo_[Section].Section_Cd INNER JOIN
           dbo_Lot_Space_Status ON dbo_Lot_Space.Lot_Space_Status_Cd =
dbo_Lot_Space_Status.Lot_Space_Status_Cd
WHERE     (dbo_Lot_Space.Location_Cd = '0104')

I've done this a lot myself, it's pretty easy to get access to do it
for you. just create a new query in Access, select SQL view and paste
the SQL block into the new query and let access tell you whats wrong
with it. You'll soon see that there isn't much to change :)

Here is what you need to do:
1.  Create a new blank query.
2.  When the window pops up to let you select a table, just click "Close".
3.  With the new query still open, on the menubar, click:
      QUERY | SQL SPECIFIC | PASSTHROUGH

4.  Paste the original SQLServer Query, AS-IS.
5.  Run it.

The pass-through option tells Access not to try to alter the SQL. 
That is exactly what you want to do.  It also will give you much
better performance results, because your SQLServer will do all the
work and will only send back the results.  If you choose to use
"Linked Tables" for all the tables in your query, then ALL the data
has to be sent to your computer from the server, and Access has to do
the linking.  This could take forever if it is a lot of data, whereas
SQLServer would be much, much faster.