Need to convert SQL query to Access query

select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM)
      ,iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription)
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from      FS_PlannerMessage m
left join FS_Item                  i  on i.ItemKey       = m.ItemKey
left join FS_ItemSupply        s  on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader         mh on mh.MOHeaderKey  = s.MOHeaderKey
left join FS_Item                  mi on mi.ItemKey      = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber

hmm...I might need labels with those fields...
select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,sItemUM: iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM)
      ,sItemDescription: iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription)
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from      FS_PlannerMessage m
left join FS_Item                  i  on i.ItemKey       = m.ItemKey
left join FS_ItemSupply        s  on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader         mh on mh.MOHeaderKey  = s.MOHeaderKey
left join FS_Item                  mi on mi.ItemKey      = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber

Ok... here is the error message I get with that one...

Syntax error (missing operator) in query expression 'i.ItemKey      = m.ItemKey
left join FS_ItemSupply       s on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader        mh on mh.MOHeaderKey   = s.MOHeaderKey
left join FS_Item                mi on mi.ItemKey     = s.ItemKey'.

OK I'm an idiot....sorry for overposting...

select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,iif(not isnull(mi.ItemUM),mi.ItemUM, i.ItemUM) as sItemNum
      ,iif(not isnull(mi.ItemDescription),mi.ItemDescription, i.ItemDescription) as sItemDescript
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from      FS_PlannerMessage m
left join FS_Item                  i  on i.ItemKey       = m.ItemKey
left join FS_ItemSupply        s  on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader         mh on mh.MOHeaderKey  = s.MOHeaderKey
left join FS_Item                  mi on mi.ItemKey      = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber

I get the exact same error message as before...

Syntax error (missing operator) in query expression 'i.ItemKey      = m.ItemKey
left join FS_ItemSupply       s on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader        mh on mh.MOHeaderKey   = s.MOHeaderKey
left join FS_Item                mi on mi.ItemKey     = s.ItemKey'.

Try adding parenthesis to define the order the of the joins:

from      ((((FS_PlannerMessage m
left join FS_Item                  i  on i.ItemKey       = m.ItemKey)
left join FS_ItemSupply        s  on s.ItemSupplyKey = m.MOLineKey)
left join FS_MOHeader         mh on mh.MOHeaderKey  = s.MOHeaderKey)
left join FS_Item                  mi on mi.ItemKey      = s.ItemKey)
where m.Planner = 'L01'

Try removing the extraneous spaces around the joins:

FROM FS_PlannerMessage m left join FS_ItemSupply s on s.ItemSupplyKey = m.MOLineKey left join FS_MOHeader mh on mh.MOHeaderKey=s.MOHeaderKey left join FS_Item mi on mi.ItemKey = s.ItemKey

Access also put parentheses around EVERYTHING, but I'm not sure you'd have to do that ...

If Access is not liking the aliases, have you tried executing the query without the aliases (ie. use full table names for all field references).  Or try adding "AS" between the table name and the alias name so that Access knows you are defining a table alias (e.g. "from [FS_PlannerMessage] As [m]").

-Paul

Ok... something different now.

When I tried to switch to design view I got the following message

Microsoft Access can't represent the join expression i.ItemKey     = m.ItemKey in Design view.
* One or more fields may have been deleted or renamed.
* The name of one or more fields or tables specified in the join expression may be misspelled.
* The join may use an operator that isn't supported in Design view, such as > or <.

I click OK and I get another dialog box saying the same thing about s.ItemSupplyKey = m.MOLineKey
same thing with mh.MOHeaderKey = s.MOHeaderKey
same thing with mi.ItemKey    = s.ItemKey

After the last error box, the query opens in design view but the tables don't have any fields and are named: m i s mh mi
Also, most of the fields on the bottom half of the screen show as expressions.

I think this is something to do with the letters (m i s mh mi)  I was told these were aliases.

Should the code be written spelling out the table names?

If so, here are the table names:
   dbo_FS_PlannerMessage
   dbo_FS_Item
   dbo_FS_ItemSupply
   dbo_FS_MOHeader

I think the aliases are as follows:
   m   = dbo_FS_PlannerMessage
   i     = dbo_FS_Item
   s    = dbo_FS_ItemSupply
   mh = dbo_FS_MOHeader

I'm not sure what the mi  is?

Paul,

I'm new to SQL and fairly new to Access.  I'm thinking this should be written without the aliases.
I'm going about this in reverse order of how I would normally do something like this.
Usually, I add linked tables to my DB, make a new query adding the appropriate tables, fields and joins.
In this case, when I do it that way, the SQL view isn't anything like what it should be (see the first post) and I don't get the proper recordset.
Most of the SQL view looks right, except for the joins.

I figured if I had someone rewrite the SQL statement so it works in Access it should make my Access query and give me the correct recordset.

Thanks,
Dan

Yes, it the tables have the "dbo" prefix, you'll have to include that in your SQL. You can certainly write this code without aliasing your tables; just leave out that part:

FROM dbo_FS_PlannerMessage left join FS_ItemSupply on ItemSupplyKey=MOLineKey left join dbo_FS_MOHeader on MOHeaderKey=MOHeaderKey left join dbo_FS_Item on ItemKey = ItemKey

The aliases (m, i, s, mh, mi) are there simply to make referencing fields easier without having to fully qualify them (i.ItemKey rather than FS_Item.ItemKey).  However, disentangling the aliases is harder for this query because it contains two different joins to the same table ...

(
left join FS_Item                  i  on i.ItemKey       = m.ItemKey
left join FS_Item                  mi on mi.ItemKey      = s.ItemKey
)

... which makes it impossible (as far as I know) to use fields from both without using aliases.  Access does support the use of aliases, but I'm almost certain you have to use the "AS" keyword, rather than simply following the table name with an alias designation.

1) Make sure that the linked table names in Access are exactly the same as is specified in the query, because Access usually adds the owner to a SQL linked table when imported (ie. FS_Item becomes dbo_FS_Item).  If the dbo (or other owner name) appears, replace all the table names in the query to fit.

2) Then try this and tell me if it works:

select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,sItemUM: iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM)
      ,sItemDescription: iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription)
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from FS_PlannerMessage as [m]
left join FS_Item as [i]  on i.ItemKey = m.ItemKey
left join FS_ItemSupply as [s] on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader as [mh] on mh.MOHeaderKey = s.MOHeaderKey
left join FS_Item as [mi] on mi.ItemKey = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber


-Paul.

Paul, I'm lost.... I tried you latest example and still got errors.

Can you or someone write this query using the table names I've listed above.
I'm trying it and getting all kinds of errors, since I really don't know SQL syntax.


Thanks,
Dan

As I said, it might not do any good because your query uses the same table twice.  You'll need to get the aliases working.  Try creating a query with Access' query designer and similar joins to see how Access does it automatically.  You'll have to develop these problem-solving skills.

With regards to the query I posted above, I realized that the calculate field syntax is off as well.  Try the code posted below and tell me exactly the error it returns.  Did you check on the table names, by the way?



Qry:
select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM) as sItemUM
      ,iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription) As sItemDescription
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from FS_PlannerMessage as [m]
left join FS_Item as [i]  on i.ItemKey = m.ItemKey
left join FS_ItemSupply as [s] on s.ItemSupplyKey = m.MOLineKey
left join FS_MOHeader as [mh] on mh.MOHeaderKey = s.MOHeaderKey
left join FS_Item as [mi] on mi.ItemKey = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber


-Paul.

Paul,

Yes, I checked the table names.

Here are the table names:
   dbo_FS_PlannerMessage
   dbo_FS_Item
   dbo_FS_ItemSupply
   dbo_FS_MOHeader

I think the aliases are as follows:
   m   = dbo_FS_PlannerMessage
   i     = dbo_FS_Item
   s    = dbo_FS_ItemSupply
   mh = dbo_FS_MOHeader

I'm not sure what the mi  is?

When I try to switch from SQL view to design view I get this error.

Syntax error (missing operator) in query expression 'i.ItemKey = m.ItemKey
left join dbo_FS_ItemSupply as [s] on s.ItemSupplyKey = m.MOLineKey
left join dbo_FS_MOHeader as [mh] on mh.MOHeaderKey = s.MOHeaderKey
left join dbo_FS_Item as [mi] on mi.ItemKey = s.ItemKey'.

Note I changed the tables names to have the dbo_ prefix before I tried to switch to design view.

Hmm, well "mi" is the alias for the second join of dbo_FS_Item (its joined using data from the itemsupply table, rather than the plannermessage table as is the first instance of dbo_FS_Item).

I think it might be because there are no brackets in your joins and at least one references another join.  Try this:

select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM) as sItemUM
      ,iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription) As sItemDescription
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from (dbo_FS_PlannerMessage as [m]
left join dbo_FS_Item as [i]  on i.ItemKey = m.ItemKey
left join dbo_FS_ItemSupply as [s] on s.ItemSupplyKey = m.MOLineKey)
left join dbo_FS_MOHeader as [mh] on mh.MOHeaderKey = s.MOHeaderKey
left join dbo_FS_Item as [mi] on mi.ItemKey = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber

Paul,

The last post was extremely close!

When I tried to change to design view I got the following message.

Microsoft Access can't represent the join expression s.ItemSupplyKey = m.MOLineKey in Design View.

*One or more fields may have been deleted or renamed.
*The name of one or more fields or tables specified in the join expression may be misspelled.
*The join may use an operator that isn't supported in Design view, such as > or <.

After clicking OK it did go into design view.
I ran the query and it gave me the expected 36 records.

I saved the query.  Closed the query and ran it again.  This time I got 116676 records.

I just reopened the query in SQL view and see that Access rewrote your code when I saved the query.

Here is your code:
select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,iif(mi.ItemUM is not null,mi.ItemUM, i.ItemUM) as sItemUM
      ,iif(mi.ItemDescription is not null,mi.ItemDescription, i.ItemDescription) As sItemDescription
      ,s.RequiredQuantity
      ,s.StartDate
      ,s.NeededDate
      ,s.DueDate
      ,s.LineStatus
      ,s.LineType
from (((dbo_FS_PlannerMessage as [m]
left join dbo_FS_Item as [i]  on i.ItemKey = m.ItemKey)
left join dbo_FS_ItemSupply as [s] on s.ItemSupplyKey = m.MOLineKey)
left join dbo_FS_MOHeader as [mh] on mh.MOHeaderKey = s.MOHeaderKey)
left join dbo_FS_Item as [mi] on mi.ItemKey = s.ItemKey
where m.Planner = 'L01'
order by m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber


Here is what it got changed to
SELECT m.Planner
      , m.MessageCode
      , m.GatewayWorkCenterCustomerID
      , mh.MONumber
      , s.LineNumber
      , m.ItemNumber
      , IIf(mi.ItemUM Is Not Null,mi.ItemUM,i.ItemUM) AS sItemUM
      , IIf(mi.ItemDescription Is Not Null,mi.ItemDescription,i.ItemDescription) AS sItemDescription
      , s.RequiredQuantity
      , s.StartDate
      , s.NeededDate
      , s.DueDate
      , s.LineStatus
      , s.LineType
FROM dbo_FS_PlannerMessage AS m
LEFT JOIN dbo_FS_Item AS i ON m.ItemKey = i.ItemKey, (dbo_FS_ItemSupply AS s
LEFT JOIN dbo_FS_MOHeader AS mh ON s.MOHeaderKey = mh.MOHeaderKey)
LEFT JOIN dbo_FS_Item AS mi ON s.ItemKey = mi.ItemKey
WHERE (((m.Planner)='L01'))
ORDER BY m.Planner, m.MessageCode, m.GatewayWorkCenterCustomerID, m.ItemNumber;


It was actually all crammed together. To make it more readable I put carriage returns in.

Also, I'm trying this remotely from home and the terminal server's version of Access is 2000
Earlier today I was trying this on my workstation which is Access 2002.

I'll try and run the same test on the Access 2002 machine to see if it will change the code or not.
I would like to get this to work with Access 2000 if at all possible, since the machine that will be running this Access report is running Access 2000.


Any ideas how to keep Access from changing your SQL query?

Thanks,
Dan