Solved

Need to convert SQL query to Access query

Posted on 2004-08-20
20
262 Views
Last Modified: 2008-02-01
Hello All,

I'm trying to recreate an SQL query in MS Access.
Here is the SQL query I run in query analyzer...

select m.Planner
      ,m.MessageCode
      ,m.GatewayWorkCenterCustomerID
      ,mh.MONumber
      ,s.LineNumber
      ,m.ItemNumber
      ,case
       when mi.ItemUM is not null
       then mi.ItemUM
       else i.ItemUM
       end ItemUM
      ,case
       when mi.ItemDescription is not null
       then mi.ItemDescription
       else i.ItemDescription
       end 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


Can someone rewrite this so it works in MS Access?

I posted this in the Microsoft SQL area first and it was suggested I post it in the MS Access area.

Thanks,
Dan
0
Comment
Question by:Die-Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +2
20 Comments
 
LVL 4

Expert Comment

by:naivad
ID: 11853980
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
0
 
LVL 4

Expert Comment

by:naivad
ID: 11853986
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
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11854000
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'.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:naivad
ID: 11854012
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
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11854041
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'.

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 11854249
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'
0
 
LVL 85
ID: 11854287
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 ...

0
 
LVL 9

Expert Comment

by:paelo
ID: 11854481
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

0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11854786
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?
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11854838
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
0
 
LVL 85
ID: 11854889
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


0
 
LVL 9

Expert Comment

by:paelo
ID: 11855284
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.
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11855429
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
0
 
LVL 9

Expert Comment

by:paelo
ID: 11855601
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.


0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11855787
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.


0
 
LVL 9

Expert Comment

by:paelo
ID: 11855932
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

0
 
LVL 9

Accepted Solution

by:
paelo earned 500 total points
ID: 11855989
OR (with all the brackets filled in)

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.
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 11857542
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


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question