Count All Records Where Record Equals Value (1 Viewer)

duncan17

Registered User.
Local time
Today, 03:31
Joined
Jan 29, 2019
Messages
10
I have a query that is getting a count of all records of a certain field from another query. I am trying to only count the number of records where that field is equal to a certain value.

Example :
[OnTime]
1
1
1
0
1
0
0
1
Count(8 records for that table)
Below is the sql that gets the count
SELECT Records.SpotNumber, Count(qGetDatesForAvg.OnTime) AS CountOfOnTime
FROM Records INNER JOIN qGetDatesForAvg ON Records.SpotNumber = qGetDatesForAvg.SpotNumber
GROUP BY Records.SpotNumber;


If I only want to count the records where qGetDatesForAvg.OnTime=1 to get Count(5 records for that table where OnTime=1), how would I do that. I can get the Count of all records from that table, but I cannot figure out how to set a criteria to only get the count where the record values = certain value. Any ideas?
 
Last edited:

Micron

AWF VIP
Local time
Today, 06:31
Joined
Oct 20, 2018
Messages
3,476
post the sql or code that you tried, otherwise we aren't sure what your field/table names are.
Is [Record] the query name, or is that the table that the query is based on?

Or assuming OnTime is a number field and not text, try
MyField: DCount("[OnTime]", "[Records]","[OnTime] = 1")
 
Last edited:

JHB

Have been here a while
Local time
Today, 11:31
Joined
Jun 17, 2012
Messages
7,732
Try the below, (it is not tested).
Code:
SELECT Records.SpotNumber, Count(qGetDatesForAvg.OnTime) AS CountOfOnTime
FROM Records INNER JOIN qGetDatesForAvg ON Records.SpotNumber = qGetDatesForAvg.SpotNumber 
WHERE qGetDatesForAvg.OnTime = 1
GROUP BY Records.SpotNumber;
 

Users who are viewing this thread

Top Bottom