Count if/where logic for query

[rdougherty]

Hello,

I am looking for a way, if there is one, to count IDs in a query based on special criteria.

I want one query to build a report record set, that alongside its overall count, has counts of records where X is true.



Results look like this:

|Program Name|Count Records|Count Online|

where, "online" means "Count records where CHannel = Online"

Can that be done so the one count exists in same query alongside the other?

Thanks,

[isladogs]

There are several ways of doing this including:

1. Create two aggregate queries, the first with an overall count & the second with the condition
Now create a new query using the above with no join

SELECT Query1.CountAll, Query2.CountConditionTrue
FROM Query1, Query2;

2. Create a query with no table source and use DCount expressions for both items

SELECT DCount("*","Table1") AS CountAll, DCount("*","Table1","Condition=True") AS CountConditionTrue;

[rdougherty]

Thanks a lot! I Was hoping there was a way of avoiding those two things, but that's okay

[isladogs]

Thanks a lot! I Was hoping there was a way of avoiding those two things, but that's okay

Avoiding which two things exactly?
You can't do it in one simple query if that's what you mean

[June7]

How does one accomplish option 2? Access query builder does not accept this structure. Must SELECT FROM a table/query.

SELECT DISTINCT DCount("*","Table1") AS CountAll, DCount("*","Table1","Condition=True") AS CountConditionTrue FROM Table1;

Possible Option 3:

SELECT Count([ID]) AS CountAll, Sum(IIf([Channel]='Online',1,0)) AS CountOnLine FROM table1;

or maybe:

SELECT ProgramName, Count([ID]) AS CountAll, Sum(IIf([Channel]='Online',1,0)) AS CountOnline FROM table1 GROUP BY ProgramName;

Or use report Sorting & Grouping features with aggregate calcs in textboxes. This will allow display of detail records as well as summary calcs.

[rdougherty]

Avoiding which two things exactly?
You can't do it in one simple query if that's what you mean

Wait... I'm confused. I just tried the following and it is counting records with a Channel of "online" correctly, alongside a normal count.

Expression:

Count(IIf([tbl_MASTER_Received]![Channel]="Online",[tbl_MASTER_Received]![Unique EA ID]))

[isladogs]

How does one accomplish option 2? Access query builder does not accept this structure. Must SELECT FROM a table/query.

Surprisingly it does work using DCount without any tables but you can only run it in SQL view - I tried it before posting to remind myself that it worked:

Design view



SQL view



Result

[isladogs]

If you try doing both in one standard aggregate query, you normally get the same value in both columns

[rdougherty]

If you try doing both in one standard aggregate query, you normally get the same value in both columns

That's what's weird - I have done it, validated it separately, and it appears to work. I have also calculated the % Online using the following:

Expression:

Count(IIf([tbl_MASTER_Received]![Channel]="Online",[tbl_MASTER_Received]![Unique EA ID]))/Count([tbl_MASTER_Received]![Unique EA ID])

These are being Grouped by Program, and the counts thus far are valid. When the data gets more complex, I will continue to validate and make sure nothing breaks.

[isladogs]

That's not an aggregate query.
I meant the type of query shown below where you use the Totals button in the ribbon
Each will work as a separate query which you can then join
However if you try and do it in one query it doesn't give the correct results
Below I'm using the same data as my previous screenshots in an aggregate query for the same table added twice



This time both results are identical - its useless for this purpose

[June7]

I see, have to click Run button from SQL View, not switch to DatasheetView by right click shortcut. Or save and open from Navigation Pane. Learn something new.

If you wanted count for each distinct value of [Channel] field (how many possible?), there would be complication but since you want only the 'Online' records counted, is simple.

I think it is still an aggregate query because aggregate functions are employed and I use the Totals button to get the Totals row. See post 5 for aggregate queries with and without GROUP BY.

[isladogs]

Yes - I agree that option 3 is also fine. In my example from the screenshots it is:

SELECT Count("*") AS CountAll, Sum(IIf([InUse]=True,1,0)) AS CountInUse
FROM PostcodeDistricts;

I suppose it's a question of semantics really - I only refer to aggregate queries where the Totals row is involved ...
I may be wrong in doing so but this link uses the term as I do: https://www.teachucomp.com/aggregate...cess-tutorial/

This MS link https://msdn.microsoft.com/en-us/lib...ffice.12).aspx refers to aggregate functions for the above type of code:

Anyway, looks like the OP is spoiled for choice on this task!

[rdougherty]

The query I gave a portion of above, is actually using the Totals/Grouping function. It is grouping by categorical data, and then is using the iif statements to qualify how to count records. I have validated it is working 100% with my data... I don't know why it wouldn't work with other data.

If someone could articulate a logic problem that would result in doing so, please do.

[isladogs]

Just for my own interest I tried the 3 methods on another postcodes table with over 2.6 million records to compare the times taken

Method 1 - using 2 aggregate queries combined with a cartesian join - took <3 seconds

Query1A: SELECT Count(Table1.FieldName) AS CountAll FROM Table1;
Query1B: SELECT Count(Table1.FieldName) AS CountConditionTrue FROM Table1 WHERE Condition=True;
Cartesian join: SELECT Query1A.CountAll, Query1B.CountConditionTrue FROM Query1A, Query1B;

Method 2 - using DCount - took 9 seconds - much longer as I expected

SELECT DCount("*","Table1") AS CountAll, DCount("*","Table1","Condition=True") AS CountConditionTrue;

Method 3 - single query using aggregate functions - took 4 seconds

SELECT Count("*") AS CountAll, Sum(IIf([InUse]=True,1,0)) AS CountInUse FROM Table1;

I was surprised to find Method 1 was faster than Method 3

I also thought it worth trying ...
Method 4 - which combines methods 1 & 3 - using 2 aggregate function queries combined using a cartesian join - it took 8 seconds
Query4A: SELECT Count("*") AS CountAll FROM Table1
Query4B: SELECT Count("*") AS CountConditionTrue FROM Table1 WHERE Condtion=True
Cartesian join: SELECT Query4A.CountAll, Query4B.CountConditionTrue FROM Query4A, Query4B;[/CODE]

All methods work & give correct answers. HTH somebody else trying to decide which method to use

[isladogs]

The query I gave a portion of above, is actually using the Totals/Grouping function. It is grouping by categorical data, and then is using the iif statements to qualify how to count records. I have validated it is working 100% with my data... I don't know why it wouldn't work with other data.

If someone could articulate a logic problem that would result in doing so, please do.

Please post the full query & I'll test an equivalent with my own data ... or is it like one of the methods 1-4 in my previous post?