Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query working in SQL Server not working in MS-Access

Posted on 2009-05-18
11
Medium Priority
?
177 Views
Last Modified: 2013-11-07
Hi Experts,
I am having my database in MS Access(I haven't work with access earlier),I write a simple join statement to retrieve data from tables but surprisingly it is throwing Syntax error.
So I created those tables in Sql and execute the same query and it is working fine there.
Is there syntax differences in Sql and Access.
If so then what should I change in following query,


select PM.Prod_Name,NV.Visit_Count,ST.Quantity,RT.Prod_Code as 'Rejection Reason'
 from
 Product_Master PM
 inner join Next_Visit NV on NV.Prod_Code=PM.Prod_Code
 left outer join Sale_Table ST on ST.Prod_Code=PM.Prod_Code
 left outer join Rejection_Table RT on RT.Prod_Code=PM.Prod_Code

Open in new window

0
Comment
Question by:johny_bravo1
[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
  • 6
  • 4
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24418523
Kindly try this one out in Access, A slight change in syntax
select PM.Prod_Na,NV.Visit_C,ST.Quantit,RT.Prod_Co as "Rejection Reason"
from
Product_Master PM
inner join Next_Visit NV on NV.Prod_Co = PM.Prod_Co
left outer join Sale_Table ST on ST.Prod_Co = PM.Prod_Co
left outer join Rejection_Table RT on RT.Prod_Co = PM.Prod_Co;

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24418538
Thanks rrjegan17 but getting same error.

Error::

Syntax Error (Missing Operator)  in query expression 'NV.Prod_Code=PM.Prod_Code
 left outer join Sale_Table ST on ST.Prod_Code=PM.Prod_Code
 left outer join Rejection_Table RT on RT.Prod_Code=PM.Prod_Cod'
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24418567
Ok.. Try this one out
select Product_Master.Prod_Na,Next_Visit.Visit_C,Sale_Table.Quantit,Rejection_Table.Prod_Co as "Rejection Reason"
from Product_Master
inner join Next_Visit on Next_Visit.Prod_Co = Product_Master.Prod_Co
left outer join Sale_Table on Sale_Table.Prod_Co = Product_Master.Prod_Co
left outer join Rejection_Table on Rejection_Table.Prod_Co = Product_Master.Prod_Co;

Open in new window

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 8

Author Comment

by:johny_bravo1
ID: 24418585
:(  still the same error
0
 
LVL 9

Accepted Solution

by:
tculler earned 1600 total points
ID: 24418599
I'm fairly certain MS Access doesn't support the "outer" keyword. I may be wrong, but this should work.

Hope it helps,
Nate
SELECT Product_Master.Prod_Na,Next_Visit.Visit_C,Sale_Table.Quantit,Rejection_Table.Prod_Co AS "Rejection Reason"
FROM Product_Master
INNER JOIN Next_Visit ON Next_Visit.Prod_Co = Product_Master.Prod_Co
LEFT JOIN Sale_Table ON Sale_Table.Prod_Co = Product_Master.Prod_Co
LEFT JOIN Rejection_Table ON Rejection_Table.Prod_Co = Product_Master.Prod_Co;

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24418608
Getting same error
Syntax Error (Missing Operator)  in query expression

Can't figure out what is wrong.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 400 total points
ID: 24418745
Can you try this one out:
select Product_Master.Prod_Na,Next_Visit.Visit_C,Sale_Table.Quantit,Rejection_Table.Prod_Co as "Rejection Reason"
FROM Product_Master
inner join Next_Visit on Product_Master.Prod_Co = Next_Visit.Prod_Co
left join Sale_Table on Product_Master.Prod_Co = Sale_Table.Prod_Co
left join Rejection_Table ON Product_Master.Prod_Co = Rejection_Table.Prod_Co;

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24418767
didn't work.
Just came across one blog,
http://nm1m.blogspot.com/2007/10/multiple-left-joins-in-ms-access.html

but still trying to achieve.
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24418891
select PM.Prod_Name,NV.Visit_Count,ST.Quantity,RT.Prod_Code as "Rejection Reason"
 from
((( Product_Master PM
 Inner Join Next_Visit NV On NV.Prod_Code = PM.Prod_Code)
Left Join Sale_Table ST On ST.Prod_Code = PM.Prod_Code)
 Left Join Rejection_Table RT On RT.Prod_Code = PM.Prod_Code);

The above query works fine.It was the matter of '('.
Thanks for your help.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24418929
Try this one as mentioned in the site provided.
select PM.Prod_Na,NV.Visit_C,ST.Quantit,RT.Prod_Co as "Rejection Reason"
FROM (((Product_Master AS PM) inner join Next_Visit AS NV on PM.Prod_Co = NV.Prod_Co)
left join Sale_Table AS ST on PM.Prod_Co = ST.Prod_Co)
left join Rejection_Table RT ON PM.Prod_Co = RT.Prod_Co;

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24419072
Yup thanks.This too works
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

598 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