How to create a summary report from multiple tables

[jannecum]

I have over 20 unlike tables in a data base and want to create a summary report that counts how many records are in each table. I can not link the tables as they are quite different in field content. Any suggestions welcome.

 

[PHV]

Perhaps an union query ?
SELECT 'Table1' As TableName, Count(*) As NumberOfRecords FROM Table1
UNION SELECT 'Table2', Count(*) FROM Table2
UNION SELECT 'Table3', Count(*) FROM Table3
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jannecum]

Thank you. I will give it a try.

 

[jannecum]

PH,

Yes this worked fine. Thank you.

 

[jannecum]

PH,

This is a short version of my union query:

SELECT 'ADMIN' As TableName,Count(*) As NumberOfRecords FROM ADMIN
UNION SELECT 'AGING', Count(*) FROM AGING
UNION SELECT 'HISTORIAN', Count(*) FROM HISTORIAN
UNION SELECT 'LAW', Count(*) FROM LAW
UNION SELECT 'Legis', Count(*) FROM Legis
UNION SELECT 'VETERANS', Count(*) FROM VETERANS;

I would also like to query a field in that is in each of these tables named 'HISTORY' and only count entries where the HISTORY field is not null. What wording do I use and where do I insert it in the above sample? Can you help me out?

 

[PHV]

SELECT 'ADMIN' As TableName,Count(*) As NumberOfRecords,Count(HISTORY) As NumberOfHistory FROM ADMIN
UNION SELECT 'AGING', Count(*),Count(HISTORY) FROM AGING
...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jannecum]

how does this count records where the HISTORY field is Null versus if it is not null. I need a report show how many records have a NULL in HISTORY and how many are not NULL. Thanks for all your help.

 

[PHV]

The number of records with Null in HISTORY may be handled by the report as (NumberOfRecords - NumberOfHistory)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jannecum]

PH,
I generated my report using info from 30 different tables and it works fine. I report on inactive history count, active history count and total count for each DEPT/TABLE. Thanks again for helping me through this.