Report based on Record Totals

  • Thread starter Thread starter antmorano
  • Start date Start date
A

antmorano

I need to create a report based on the total amount of records there
are in about 8 different queries. I don't need any of the information
except the total amount of records that there are. Any help would be
appreciated.

-Anthony Morano
 
SELECT COUNT(*) As countInQ1,
(SELECT COUNT(*) FROM q2) As countInQ2,
(SELECT COUNT(*) FROM q3) As countInQ3,
...
(SELECT COUNT(*) FROM q8) As countInQ8
FROM q1


where I assume the queries name are q1, q2, ... q8


If you want 8 rows, rather than one, try:


SELECT COUNT(*) As myCount, 1 as queryNumber FROM q1
UNION ALL
SELECT COUNT(*), 2 FROM q2
UNION ALL
....
SELECT COUNT(*), 8 FROM q8
ORDER BY queryNumber ASC



where the second column gives the query (number) the count belongs to.



Vanderghast, Access MVP
 
Nope, as a query, in SQL view. Create a new query, switch into SQL view.



Sure, replace the ... by the sequence (involving q3, q4, a5, q6, q7), and
use the real query names, and field names.



Hoping it may help,
Vanderghast, Access MVP
 
When I input it and change the query names I get a syntax error from
FROM. Here is my SQL

SELECT COUNT(*) As myCount, 1 as queryNumber FROM q<65 H&w (I-IV)
UNION ALL
SELECT COUNT(*), 2 FROM q<65 H&W (V)
UNION ALL
SELECT COUNT(*), 3 FROM q<65 H&W (VI)
ORDER BY queryNumber ASC
 
Assuming that everything after the FROM is the name of a query or a
table, you need to surround the names with square brackets [query Name]

ELECT COUNT(*) As myCount, 1 as queryNumber FROM [q<65 H&w (I-IV)]
UNION ALL
SELECT COUNT(*), 2 FROM [q<65 H&W (V)]
UNION ALL
SELECT COUNT(*), 3 FROM [q<65 H&W (VI)]
ORDER BY queryNumber ASC


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
JOHN- THANK YOU SO MUCH FOR YOUR HELP. I GOT THE QUERY TO RUN
BEAUTIFULLY. I REALLY APPRECIATE YOUR PATIENCE.

-ANTHONY MORANO
PENSION FUND INTERN
 
Back
Top