Solved

Need to convert SQL query to Access query

Posted on 2004-08-20
20
257 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
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
 
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 50

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 84
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
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
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 84
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

919 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

12 Experts available now in Live!

Get 1:1 Help Now