counting the number of records in a query

[msfirsttrust]

I have several queries for which I need a count of the number of records. Is it possible to make one query that can give me a count for each individual query, or do I have to do them separately?

 

[apr pillai]

You can get the count of records in a Query or Table with the DCount() Function, like DCount("*","QueryNumber1"). You may write this in a Query Column like Count1:DCount("*","QueryNumber1") and create several columns in similar way for all other Queries.

But, why you need the count of records in another query?

 

[msfirsttrust]

when i did this, I got an error that said the query was too complex.

The reason I need to do this is I have one audience that gets the results of each query, then another audience that just wants counts. I was hoping to build a query for the counts, so I don't have to manually provide that information.

 

[Will_B]

Hi, msfirsttrust. Do you know how to create a module in MS Access? If so (or you are otherwise interested), I can send you a short procedure (VBA code) that will count any list of queries you have and put the results in an email message that you can then edit, address, and send. It uses the DCOUNT method that apr pillai suggested you use.

If you are interested, let me know:
1) Do you need instructions on how to create a new module?
2) Which version of Access are you using? I'll tailor my instructions to it.
3) Do those queries that need counting have a naming covention (e.g., MyQuery_1, MyQuery_2) or is there no pattern to the query names? If there is a pattern, tell me a couple of names so I can see the pattern.

If you aren't interested, my feelings won't be hurt.

 

[james_lankford]

as rows

select 
  count(*) as q1count
from
  query1

union 

select 
  count(*) as q2count
from
  query2

or the much harder
as columns

select
  
  q1.q1count, 
  q2.q2.count
  
from

(
  select
    count(*) as q1count,
    1 as dummyfield 
  from 
    query1
) as q1

inner join 

(
  select
    count(*) as q2count,
    1 as dummyfield 
  from 
    query2
) as q2

on 

q1.dummyfield = q2.dummyfield