Counting the total number of records in a query

U

US_Soldier315

How could i have access count the total number of records in a specific query?
 
U

US_Soldier315

It has been a long time since I have had to do this stuff. where do i need
to put that function?
 
U

US_Soldier315

Let me tell you what i am trying to do and you might be able to help me better.

I have 3 queries: Enlisted, Warrant, & Officer.
I am trying to create another query that will tell me how many records are
in each query. Is that possible? It has been a while since I have done this
stuff.
 
M

Michael Gramelspacher

A union query might work

SELECT 'Enlisted' AS Category, Count(*) AS [Category Count]
FROM (Select * FROM Soldiers Where Soldiers.service_category = 'Enlisted')
UNION ALL
SELECT 'Officer', Count(*) AS [Category Count]
FROM (Select * FROM Soldiers Where Soldiers.service_category = 'Officer')
UNION ALL
SELECT 'Warrant', Count(*) AS [Category Count]
FROM (Select * FROM Soldiers Where Soldiers.service_category = 'Warrant');

but maybe simply:

SELECT Soldiers.service_category,
COUNT(* ) AS [Category Count]
FROM Soldiers
GROUP BY Soldiers.service_category;

73C
 
F

fredg

Let me tell you what i am trying to do and you might be able to help me better.

I have 3 queries: Enlisted, Warrant, & Officer.
I am trying to create another query that will tell me how many records are
in each query. Is that possible? It has been a while since I have done this
stuff.
Click to expand...

I'll use 2 queries to show as an example, but you can use 3.
Why are you doing this in a query. I would guess that you would want
this count to be shown either on a form or in a report.
In which case, use an unbound control.
Set it's control source to:
= "Count Enlisted " & DCount("*","Enlisted")

Do similar for each other query count needed.
Or, you could combine them into one control:

= "The Enlisted count is " & DCount("*","Enlisted") & " and the
Officer count is " & DCount("*","Officer ")

You could do it in a query:
Type directly into the SQL View of a NEW query, the following:

SELECT DCount("*","Enlisted") AS CountEnlisted,
DCount("*","Officer") AS CountOfficers;

You will get 1 record with 2 columns showing just the total count from
each query.
 
Top