Query a recordset?

[muster]

I'm struggling with ADO recordesets. This question confused me: can I
"query on a recordset"?

I mean, first I got a recordset from a table, then I want to
manipulate the data in the recordset a little bit, say doing sum,
etc., is that possible? Do I need to create temp tables to store the
data? I guess must be a better way.

Thanks,
Muster

 

[John W. Vinson]

I mean, first I got a recordset from a table, then I want to
manipulate the data in the recordset a little bit, say doing sum,
etc., is that possible? Do I need to create temp tables to store the
data? I guess must be a better way.

Why the extra step?

You can create Totals queries *based directly on the table* using whatever
criteria you would use to generate the recordset, and do your totals, etc.
right then and there.

John W. Vinson [MVP]

 

[muster]

Why the extra step?

You can create Totals queries *based directly on the table* using whatever
criteria you would use to generate the recordset, and do your totals, etc.
right then and there.

John W. Vinson [MVP]

I think you are right and I'm just confused. The situation is there
are some "queries on queries" from old work and I just want to
replicate them for now. I know I should change the queries. On the
other hand, is it possible when "query on query" is necessay and I
want to do that with recordset?

Thanks,
Muster

 

[John W. Vinson]

I think you are right and I'm just confused. The situation is there
are some "queries on queries" from old work and I just want to
replicate them for now. I know I should change the queries. On the
other hand, is it possible when "query on query" is necessay and I
want to do that with recordset?

It's certainly possible to create a Query based on a Query, and there is no
need to create a Recordset object to do so. You can do it in the query grid by
simply selecting the query name as the source; the "Show Table" popup window
offers Tables, Queries or Both. Or you can use the query name in the SQL
window as if it were a table.

John W. Vinson [MVP]

 

[Jamie Collins]

The situation is there
are some "queries on queries" from old work and I just want to
replicate them for now. I know I should change the queries. On the
other hand, is it possible when "query on query" is necessay and I
want to do that with recordset?

What do you mean by 'query'? You can the ADO recordset's Filter
property to simulate a search condition (WHERE clause) using basic
operators. You can use the SHAPE syntax to create a hierarchical ADO
recordset e.g. one of the recordset Fields is a recordset containing a
different set of data etc.

It's likely, however, that you are applying a procedural code mindset
to the task. SQL is a set-based declarative language, consequently a
SQL statement is more like a specification -- you should be aiming to
complete you operation with one 'line' of SQL

Jamie.

--