Aggregrte Funtion

  • Thread starter Thread starter Mark via AccessMonster.com
  • Start date Start date
M

Mark via AccessMonster.com

Count Aggregate Function

I have multiple independent queries and I need to get the number of records
each of them produced all in one query. I tired union all and was able to get
the required results but was wondering if there is a better way to get the
required data.

For example
Query 1 resulted in 100 records
Query 2 resulted in 25 records
Query 3 resulted in 10 records

The output should look like:
Query Name Number of Records
------------------------------------------------------------------------------
--
Query 1 100
Query 2 25
Query 3 10

Thank you,
 
I can't take the credit for this one. Someone else came up with it for the
number of records in the tables. Possibly it was Duane.

SELECT msysobjects.Name, DCount("*",[Name]) AS Records
FROM msysobjects
WHERE msysobjects.Name Like "Query*"
AND msysobjects.Type In (5)
ORDER BY 1;

If you can, put in the proper name beginning in the Like clause or all the
needed query names in an In clause such as

In("Query 1","Query 2","Query 3")
 
Thank you very much Jerry that worked but it is taking the same amount of
time so I am not sure if there is any other way.

Jerry said:
I can't take the credit for this one. Someone else came up with it for the
number of records in the tables. Possibly it was Duane.

SELECT msysobjects.Name, DCount("*",[Name]) AS Records
FROM msysobjects
WHERE msysobjects.Name Like "Query*"
AND msysobjects.Type In (5)
ORDER BY 1;

If you can, put in the proper name beginning in the Like clause or all the
needed query names in an In clause such as

In("Query 1","Query 2","Query 3")
Count Aggregate Function
Click to expand...
[quoted text clipped - 17 lines]
Thank you,
Click to expand...
Click to expand...
 
Back
Top