[AccessD] Select Count(*) from different tables

Arthur Fuller artful at rogers.com
Sun Aug 28 17:14:44 CDT 2005


That is one potential approach but not the one I had in mind. What I had in
mind was more like this. Assume BE1 and BE2, both linked to the FE that does
this.
Select Count(*) From BE1.Table1, Count(*) From BE2.T1
Select Count(*) From BE1.Table2, Count(*) From BE2.T2

Actually, this is not necessary since there are queries that use the
non-duplicate thingie to return the rows not present in BE2 that are present
in BE1. I then use this query result to do an append query to BE2. 
At this moment, I can do what I need to do using Dcount(), but it sucks, in
terms of performance. It works, but it is SLOW.
For each pair of tables (call the abstract T1_DS and T1), I want the
rowcount from each and to report the difference. And I want to do these
differences for all the tables of interest. Assume about 25 pairs of tables.
The Union approach gives me rows. I want columns, because I want to display
the results on one "row" of one results form.
A.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: August 28, 2005 6:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Count(*) from different tables

On 28 Aug 2005 at 8:37, Arthur Fuller wrote:

Not sure quite what you mean bye compair the count of "each pair of tables" 
since you also say "I want to compare about two dozen tables"

Is this what you are talking about?
 
SELECT  "AssessID_Notes" as QName,  Count(*) 
 FROM [AssessID_Notes_DS Without Matching AssessID_Notes],

UNION

SELECT "Bill_Time_Machine",  Count(*) 
 FROM [Bill_Time_Machine_DS Without Matching Bill_Time_Machine])

UNION

SELECT "Description","Count(*)
FROM [qryMyNextQuery]


> 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: 
> 

> 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.) 

-- 
Stuart


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




More information about the AccessD mailing list