[AccessD] Select Count(*) from different tables

You need subquerries for the second (third, fourth, etc) 'counts'

Drew

-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com]
Sent: Sunday, August 28, 2005 7:38 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Select Count(*) from different tables


I have two MDBs identical in structure (or nearly so) and I want to compare
the counts of each pair of tables and put the results all in a single query.
I know that I have done this before, but I can't quite remember the syntax I
used. It is something along the lines of:
SELECT 
Count(*) AS AssessID_Notes_Count
FROM [AssessID_Notes_DS Without Matching AssessID_Notes],
(SELECT Count(*) AS Bill_Time_Machine_Count
FROM [Bill_Time_Machine_DS Without Matching Bill_Time_Machine])

But the above returns only one column, not two as I would expect. Since I
want to compare about two dozen tables (queries, actually), I stopped at two
until I could get it right. Removing the parentheses from the second
"column" results in an error.
Can anyone suggest the correct syntax for this type of query? (This case is
Access MDB not ADP, but it might be useful to have both answers handy.)
TIA,
Arthur

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com