Summarising a large number of queries

  • Thread starter Thread starter Dan_S
  • Start date Start date
D

Dan_S

I am trying to create some way of reporting the total values in a large
number of Queries. All I need to do is to count the number of records
returned by each Query, and then display them in some meaningful format, say
in a report or another Query itself.

Attempting to do this in a Query or Report so far has been unsuccessful,
because Access gives an error regarding being unable to link fields.

Would it be possible to carry this procedure out in Access using VB or a
Macro?

Many Thanks
 
Where it says 'This is query 1' replace it with the name of the query.


SELECT 'This is query1' AS Query, Count(*) AS Records
FROM query1

UNION All

SELECT 'This is query2' AS Query, Count(*) AS Records
FROM query2;
 
Thanks for that! I've plumbed that Query in, but unfortunately it tells me
that it's 'too complex'. Is there any way around this, or will I have to
split the Query up?
 
The union query will only work if the fields for each query have the same
information and data type.

If your queries are very different from each other you may not have much
choice.
 
You MIGHT be able to do something like the following.

SELECT DCount("*",[Name]), [Name]
FROM MsysObjects
WHERE Type = 5 AND Flags = 0

You will get an error for any parameter queries. And there may be other
places where this will fail.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top