Build Query that Joins Two Queries

[wandan]

Hello,

I am using Access 2010 with VBA code. I need to create a SQL statement that I can use for docmd.RunSQL (strSQL). I know I have done this in the past but haven't been able to figure it out.

I have Query1 and Query2. Then, I need to do a LEFT JOIN of the two results. Below is basically what I am trying to do. Does anyone know what the proper syntax would be?

SELECT a.id, a.cost, b.invoices FROM
(SELECT id, sum(cost) as cost FROM table1)a
LEFT JOIN
(SELECT id, count(invoices) as invoices FROM table2)b
ON a.id = b.id

Thanks for any help!!!

 

[PHV]

FYI, the DoCmd.RunSQL method should be used only for ACTION queries ...
Furthermore your both subqueries lack the GROUP BY clause.

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

 

[wandan]

That makes sense. Currently I have two access queries (not the two in my sample - that was just to illustrate a point) that look to a form for some criteria. Then, I have been trying to create a third one that left joins those two queries. This third query doesn't work because it can't seem to recognize the values from the form. So I turned to VBA to create the SQL on the fly with the value from the form, run the query, then loop to the next record. Is there anyway to replicate this in VBA? Or do you have any suggetions for making the query work?

Thanks again!

 

[MazeWorX]

Open the Form ... Open the query in design view ... right click in the criteria field and select build. goto Forms: open forms: and locate your controls dbl click the found control or hit paste after it has been selected hit OK and the reference to your form has been added to your query.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[wandan]

That's what I have now, but it doesn't work. To clarify, it works when I run the query the form reference is in. It doesn't work when I use that query as the source in another query.

 

[MazeWorX]

it will if the form is open

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[wandan]

Thanks for the help. I have narrowed down that it is a Crosstab query that is causing the problem.

 

[dhookom]

wandan,
Stop making everyone guess. Provide the SQL or code or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

if its returning no data then its a Join or possibly a GroupBY clause issue as PHV has suggested

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

dhookom

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[PHV]

it is a Crosstab query
So, you have to use the PARAMETERS instruction in the SQL code.

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

 

[dhookom]

Check out about the third from the last post in

http://www.tek-tips.com/viewthread.cfm?qid=1670495

which I just posted today.

Duane
Hook'D on Access
MS Access MVP