VBA and stacked queries

[talkto_menow]

I hope somebody could clarify this for me and point me into the right
direction.

I think most of people are familiar with a concept of stacked queries,
they can also be called "query on query" They allows to extract
specific field or transform data. So the question is rather general.

Sample queries

QueryA:

SELECT Account, Date, Amount
FROM Table1

QueryB:

SELECT Date, SumOfAmount
FROM QueryA

Is there a way to accomplish this using VBA code and get the results of
QueryB?
How the results of the QueryA can be passed to QueryB within VBA code?

Thanks, any help would be appreciated

 

[Pinchas Tishyn]

Stacked queries work in VBA as well, as long as you save Query A before
running the code.

Dim rec as DAO.recordset

Set rec=CurrentDb.Openrecordset("SELECT Date, SumOfAmount FROM QueryA")
<...do whatever you need with the recordset...>
rec.Close
set rec=Nothing

 

[talkto_menow]

Thank you for prompt response. I have additional question. Assuming
that QueryC would be based on QueryB and QueryA is already saved, would
I need to define another recordset with its own SQL statement?

Let say

QueryC:

SELECT Date
FROM QueryB

Thanks

 

[John Spencer]

You could use a subquery as long as your field and table names consisted of
only letters and numbers (no spaces or other characters). You would also
need to fully qualify any field names (add the table name) that are reserved
words (such as Date).

SELECT A.Date, SumOfAmount
FROM
(SELECT Account, Table1.Date, Amount
FROM Table1) As A

Else thread you asked about a third query.

SELECT B.Date
FROM (

SELECT A.Date, SumOfAmount
FROM
(SELECT Account, Table1.Date, Amount
FROM Table1) As A
) As B