Solved

Need to convert SQL query to Access query

Posted on 2004-08-20
20
254 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
  • 7
  • 5
  • 3
  • +2
20 Comments
 
LVL 4

Expert Comment

by:naivad
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:naivad
Comment Utility
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
Comment Utility
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 50

Expert Comment

by:Steve Bink
Comment Utility
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 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Author Comment

by:Die-Tech
Comment Utility
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 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now