Convert SQL to Access SQL Query 1

[sandylou]

I can easily create a query in SQL SERVER but the application is in ACCESS. I have the following query:

select 
P.Product_Number 
,PG.Program_Type 
,PG.Program_Name 'Grant Name'
,PG.Program_Start_Date 'Grant Start Date'
,PG.Program_End_Date 'Grant End Date'
,PP.Program_Type 'Project Program'
,PP.Program_Name 'Project Name'
,PG.Program_Start_Date 'Project Start Date'
,PG.Program_End_Date 'Project End Date'
from Product_Request P
left join Program PG on PG.Product_Number = P.Product_Number
		AND PG.Product_Type = 'G'
left join Program PP on PP.Product_Number = P.Product_Number
		AND PP.Program_Type = 'P'

I run it in SQL SERVER and it runs perfect. I cut and paste into Access and it says the join is not supported. I am overcomplicating it or something in ACCESS because I can't get it to work. No I can't move it into SQL SERVER right now. I would like to, would make everything easier.

 

[HarleyQuinn]

Not meaning to sound a bit harsh, but have you tried the access help files for the LEFT JOIN operator?

Sorry if tht sounds a bit unhelpful

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

SELECT
P.Product_Number
,PG.Program_Type
,PG.Program_Name [Grant Name]
,PG.Program_Start_Date [Grant Start Date]
,PG.Program_End_Date [Grant End Date]
,PP.Program_Type [Project Program]
,PP.Program_Name [Project Name]
,PG.Program_Start_Date [Project Start Date]
,PG.Program_End_Date [Project End Date]
FROM (Product_Request P
LEFT JOIN Program PG on PG.Product_Number = P.Product_Number)
LEFT JOIN Program PP on PP.Product_Number = P.Product_Number
WHERE Nz(PG.Product_Type,'G') = 'G'
AND Nz(PP.Program_Type,'P') = 'P'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sandylou]

I ended up doing it like this:

FROM (Product_Request P
LEFT JOIN (Select * from Program Where Program_Type = 'G') PG  ON PG.Product_Number = P.Product_Number)
LEFT JOIN (Select * from Program Where Program_Type = 'P') PP  ON PP.Product_Number = P.Product_Number

So I guess I should look up joins in Access. Because I am trying to join a 4th table and I get a syntax error.

FROM (Product_Request P
LEFT JOIN (Select * from Program Where Program_Type = 'G') PG  ON PG.Product_Number = P.Product_Number)
LEFT JOIN (Select * from Program Where Program_Type = 'P') PP  ON PP.Product_Number = P.Product_Number
Left Join (Select * from X) X on X.Product_Number = P.Product_Number

Thanks for your help. I did look up in access help, but didn't see I know I can do this with multiple queries in access, but I am trying to keep the code in one query.

 

[PHV]

FROM [!]([/!](Product_Request P
LEFT JOIN (Select * from Program Where Program_Type = 'G') PG ON PG.Product_Number = P.Product_Number)
LEFT JOIN (Select * from Program Where Program_Type = 'P') PP ON PP.Product_Number = P.Product_Number[!])[/!]
Left Join (Select * from X) X on X.Product_Number = P.Product_Number

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sandylou]

THANKS - I guess I never realized that is how you can do multiple joins is to use the parens. Is this in the help or just years or experience?

 

[cmmrfrds]

Access uses a little different syntax on derived tables when coding the SQL. It uses []. instead of () - at least it did in the older versions.

For example the X table.
Left Join [Select * from X]. X on X.Product_Number = P.Product_Number