query a recordset

[GerryGoldberg]

I would like to select some records like:

set rst1 = dbs.openRecordset("SELECT * FROM tableA WHERE state = 'OH')

Then, based on the resulting recordset, I would like to do an additional query, like:

set rst2 = "COUNT (*) FROM (the recordset rst1) WHERE City='CLEVELAND'"

How can I reference rst1 in my second query?

Thanks,

Gerry Goldberg

 

[Aivars]

Hi, Gerry!

Why don't you create one query with both criterias?

set rst1 = dbs.openRecordset("SELECT Count(*) AS RecordsCount FROM tableA WHERE state = 'OH' AND City='CLEVELAND'&quot

Aivars

 

[GerryGoldberg]

This example is only one of a set of operations that I have to do with this data. For example, I need to establish a base recordset of data from a specific state, e.g. rst1. Then I need to extract a number of different kinds of data from this recordset, such as average age of those clients from Cincinnati, or types of housing for those clients in Toledo, etc. I'm trying not to have to re-create the base recordset over again for each subsequent set of data that I need. Would this be easier to use filtering with the recordsets?

Thanks,

Gerry Goldberg