SQL statement joined to saved query

[kschuster]

I'm using VBA to generate a dynamic sql statement. In Access Query designers you can relate a table to a query result. that would look like

SELECT tblStateTax.StateAbbr, SOurce_Recon.Source_Count, SOurce_Recon.Source_Cost
FROM tblStateTax LEFT JOIN SOurce_Recon ON tblStateTax.StateAbbr = SOurce_Recon.State;

WHERE - Source_Recon is the Query and tblStateTax is the TABLE

How do I write the SQL to that contains the actual sql from Source_Recon (a saved query) into the sql above

 

[JoeAtWork]

I can't imagine why you would need to do that, since you could just use Source_Recon the same you would a table.

Whether it's possible highly depends on what kind of SQL statement Source_Recon is. For example if it's a cross-tab query or a query with aggregates (SUM, MIN, MAX, etc.), then you can't mix in Source_Recon's SQL with tblStateTax and have it come out the way it does with your current statement. If Source_Recon is a very simple SELECT statement based on one table, you could pretty much use the statement you already have but just replace every instance of "Source_Recon" with the base table name (plus adding whatever WHERE clause you might have in Source_Recon).

But again, why not just use Source_Recon as is?

 

[Crowley16]

you can use a derived table...

select
*
from
tableA inner join
(
select * from tableB inner join tableC on ...
) as tableD


--------------------
Procrastinate Now!

 

[kschuster]

It is not really saved query as it is dynamically created via VBA

The query Source_Assets looks like this

SELECT Source_Locations.State, Count(Source_Assets.EMCOST) AS Source_Count, Sum(CCur([EMCOST])) AS Source_Cost
FROM Source_Locations INNER JOIN Source_Assets ON Source_Locations.[New PC#] = Source_Assets.EMCLOC
GROUP BY Source_Locations.State;

 

[kschuster]

Got IT


select
tblStateTax.StateAbbr, SOurce_Recon.Source_Count, SOurce_Recon.Source_Cost
from
tblStateTax Left Join
(
SELECT Source_Locations.State, Count(Source_Assets.EMCOST) AS Source_Count, Sum(CCur([EMCOST])) AS Source_Cost
FROM Source_Locations INNER JOIN Source_Assets ON Source_Locations.[New PC#] = Source_Assets.EMCLOC
GROUP BY Source_Locations.State
) as Source_Recon

ON tblStateTax.StateAbbr = Source_Recon.State

Perfect
Thanks