Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Query working in SQL Server not working in MS-Access

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
Johny Bravo
Asked:
Johny Bravo
  • 6
  • 4
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Johny BravoAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Johny BravoAuthor Commented:
:(  still the same error
0
 
tcullerCommented:
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
 
Johny BravoAuthor Commented:
Getting same error
Syntax Error (Missing Operator)  in query expression

Can't figure out what is wrong.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Johny BravoAuthor Commented:
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
 
Johny BravoAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Johny BravoAuthor Commented:
Yup thanks.This too works
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now