Sub Queries using VBA (2000 (9.0.4402 SR-1))

Topic

New Reply

Having a problem creating subqueries.

I have a query which summaries Premiums by class of business and by unique policy reference and then I have a second query which extracts from the first the count of the number of unique polices by class of business along with the summary of the premiums. I do it this way, because the table the first run pulls from has multiple transactions for the same policy and to perform the count on the first run distorts the output. The second query

Reply | Quote

Replies

See post 401925 for instructions on how to reduce the size of a database for posting.

Reply | Quote

Ah Ha!

ZIpped db as attached.

Reply | Quote

It may be possible to do this with an inline subquery, but I didn’t manage to make that work.

I have attached a modified version that modifies a query in code, then runs the append SQL on this query. This is more efficient than using an intermediate table.
The code uses DAO, I set a reference to the Microsoft DAO 3.6 Object Library in Tools | References…

Reply | Quote

Many thanks, good stuff!

Is there a way of using the ADO architecture to do this?

Cheers,

Niven

Reply | Quote

In theory, it should be possible to use ADOX for this, but I when I tried it, I couldn’t change the CommandText of the View object. Perhaps someone else knows how to do this.

Reply | Quote