Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-29-2019, 12:14 PM   #1
duncan17
Newly Registered User
 
Join Date: Jan 2019
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
duncan17 is on a distinguished road
Smile Count All Records Where Record Equals Value

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 by duncan17; 01-29-2019 at 12:38 PM.
duncan17 is offline   Reply With Quote
Old 01-29-2019, 12:22 PM   #2
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,247
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Count All Records Where Record Equals Value

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 by Micron; 01-29-2019 at 12:29 PM. Reason: clarification
Micron is offline   Reply With Quote
Old 01-29-2019, 09:05 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Count All Records Where Record Equals Value

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;

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
access , count

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
When value equals 0, record disappears mpaulbattle Queries 8 12-01-2017 03:45 AM
Find a record that equals 95% of total records joehands1980 Queries 3 11-25-2016 02:51 PM
I only want records whose sum equals a field sharrell Queries 2 09-23-2002 04:07 AM
Repeating reports equals number of records in subreport?? bri822 Reports 2 06-04-2002 08:21 PM
[SOLVED] Record count for no records entered dmdata Reports 1 06-18-2001 02:10 PM




All times are GMT -8. The time now is 03:31 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World