[AccessD] Select Count(*) from different tables

Arthur,

    The solution suggested in my previous post is meant to ensure an output of just one row, the number of columns being equal to the number of tables in question.

Best wishes,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, August 29, 2005 03:44
  Subject: RE: [AccessD] Select Count(*) from different tables


  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