Counting with criteria a Query (1 Viewer)

elawrence

New member
Local time
Today, 19:22
Joined
Feb 11, 2021
Messages
3
Hi everyone

I'm very much a low-level Access user and I am struggling with something I'm sure must be very simple. I am happy with using the Totals function to count records within a query, but when I come to adding criteria (for example: only count the record if a field within the record is a certain value) I just cannot make it work.

In a Planning Application database, I have a self-calculating field (using an IIf statement) in a table that is either 1 or 0, [InAgreement]. This is dependent on two dropdown box fields within the same table having opposing answers.

For example (in basic logic): If [OurPlanningOfficer] = "Disagree" And [TheirPlanningOfficer] = "Agree" Then [InAgreement] = 1.

In my table, [InAgreement] works perfectly, returning either 1 or 0, dependant on the dropdown fields.

In my query, I want to calculate the number of times the officers have not been in agreement over a date range so that I can display this as a one-off statistic in a report header (report based on this query). My query displays the correct number of records within a specified date range ([ReceivedDate] query criteria is: >=[Forms]![Main Menu]![Start Date] And <=[Forms]![Main Menu]![End Date]...

...but as soon as I add criteria to count ONLY the records where [InAgreement] = 1, the query returns nothing at all.

Please can anyone help me with the query criteria syntax to only count where [InAgreement] = 1. Please see the attached image, which is my latest attempt! I've tried using Count in the Totals box, then '=[InAgreement] = 1' for the Criteria, I've tried using the Criteria 1, or "1", or =1, or True, or ="True" etc etc...but nothing works!

Thanking you in advance.
 

Attachments

  • QueryImage.jpg
    QueryImage.jpg
    50.4 KB · Views: 586

bastanu

AWF VIP
Local time
Today, 12:22
Joined
Apr 13, 2010
Messages
1,401
Try to get rid of the last column, change the Totals row for InAgreement to Sum instead of Count and put only 1 in the criteria row (not =1).

Cheers,
 

plog

Banishment Pending
Local time
Today, 14:22
Joined
May 11, 2011
Messages
11,611
Can you post a sample of your database with just the objects I need to see the issue? Don't give me something with 20 tables, 18 queries, 27 forms and 39 reports.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:22
Joined
Sep 12, 2006
Messages
15,614
You would probably be better having [in agreement] as a true/false Y/N field.
But then, note that true is not plus 1, it's -1 (minus 1), and anything else (but normally 0, zero) is false.

Anyway, allowing-1 to be "true" would be a good habit to get into
 

elawrence

New member
Local time
Today, 19:22
Joined
Feb 11, 2021
Messages
3
Can you post a sample of your database with just the objects I need to see the issue? Don't give me something with 20 tables, 18 queries, 27 forms and 39 reports.
Please see the attached database. I created this with only the basics needed to perform this. As you will see, in tblAgreementRecord there are four records, with two of those records showing a value of 1 for Agreement.

I simply want the query to count the number of instances where the value of Agreement is 1, and return a single figure that I can include as a statistic in a report header.

I have tried the suggestion above from user bastanu, as you will see in the query, but it doesn't return what I want. I did wonder if this calculation could be done in the report, using an unbound textbox. However I fell down again with the syntax. The query needs to return all records to display on the report, but then only count the records that have Agreement as 1.

Thank you.
 

Attachments

  • TestDatabase.accdb
    608 KB · Views: 547

bastanu

AWF VIP
Local time
Today, 12:22
Joined
Apr 13, 2010
Messages
1,401
If you don't want to group by and simply need all agreements =1 then here is the SQL:

SELECT Sum(tblAgreementRecord.Agreement) AS TotalAgreements
FROM tblAgreementRecord
WHERE (((tblAgreementRecord.Agreement)=1));
 

plog

Banishment Pending
Local time
Today, 14:22
Joined
May 11, 2011
Messages
11,611
Your tables aren't set up properly, I would fix that issue before proceeding to whatever this query's purpose is.

1. Calculated fields shouldn't be used in Tables. Instead you should do the calculation in a query and reference that query when you need the value. That means tblAgreementRecord shouldn't have [OurResponse], [TheirResponse], nor [Agreement] fields.

2. Use the correct field type for fields. [OurPlaningOfficer] and [TheirPlanningOfficer] can only have 2 values, so instead of having them use tables to link to their values, instead you should use a Yes/No field type for those fields. Then you can rename them to match their values: [OurOfficerPermitted] , [TheirOfficerPermitted] and then a Yes in the box means it was permitted by the corresponding officer.

With that structure you can then create a query to do the calculations you need for furtther queries:

Code:
SELECT ID, RecordName, OurOfficerPermitted, TheirOfficerPermitted, IIf(OurOfficerPermitted = TheirOfficerPermitted, 1,0) AS Agreement
FROM tblAgreementRecord

Then you can do a simply Totals query on the above query to get all the data you need. Simply SUM up the Agreement field (without any criteria) and you can calculate all the agreements.
 

elawrence

New member
Local time
Today, 19:22
Joined
Feb 11, 2021
Messages
3
Thanks everyone. Some really good stuff to work on, I clearly have much to learn! I will try these suggestions and see how I get on.
 

Users who are viewing this thread

Top Bottom