Count IF

  • Thread starter Thread starter Emlou85 via AccessMonster.com
  • Start date Start date
E

Emlou85 via AccessMonster.com

Hi

I want to be able to create a report which shows whether a pupil passed their
driving test or not, which I have managed to do (by grouping by Customer_ID).
I then want the report to show on what attempt they passed their test, which
I have been semi successful with - by altering the query I can Count the
Customer ID. So, so long as they have passed their test this works fine, as
it counts all the records and therefore attempts for that customer. However,
if they have not yet passed their test it is still counting the records,
which I don't want it to do. Ideally I think I need it to be a Count IF Yes
they have passed their test, but to count all the records for that customer
ID. As obviously if I enter 'Yes' as the criteria in the Passed test field
it will only return 1 result per pupil, which won't show me on what attempt
they successfully passed. I hope this makes sense! Any ideas I would be
most grateful!

Thanks in anticipation
Emma

Emma
 
Emma:

There are a couple of ways you could handle this:

1. Count all the rows for the customer on or before the one on which the
Passed column is TRUE. To identify the row you'll need something like a
TestDate column in the Tests table. Given that you can do this with
subqueries like this:

SELECT CustomerName,
(SELECT COUNT(*)
FROM Tests AS T1
WHERE T1.CustomerID = Customers.CustomerID
AND T1.TestDate <=
(SELECT TestDate
FROM Tests AS T2
WHERE T2.CustomerID = T1.CustomerID
AND T2.Passed = TRUE))
AS Attempts
FROM Customers;

This query lists each customer and contains a subquery in its SELECT clause
which returns the count of rows in the Tests table. The subquery itself
contains a subquery in its WHERE clause which returns the TestDate when the
customer passed. This subquery is correlated with the first subquery on the
CustomerID and each instance of the Tests table is differentiated by giving
then separate aliases T1 and T2. This means that the first subquery only
counts the rows for the current customer in the outer query where the
TestDate is on or earlier than that where the passed column is TRUE.

2. Count ALL the rows for just those customers who have passed, and by
using a UNION ALL operation also return those customers who have not passed,
but in their case return a NULL count:

SELECT CustomerName, COUNT(*) AS Attempts
FROM Customers INNER JOIN Tests
ON Customers.CustomerID = Tests.CustomerID
WHERE EXISTS
(SELECT *
FROM Tests
WHERE Tests.CustomerID = Customers.CustomerID
AND Passed = TRUE)
GROUP BY CustomerName
UNION ALL
SELECT CustomerName, NULL
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM Tests
WHERE Tests.CustomerID = Customers.CustomerID
AND Passed = TRUE);

Each part of the UNION ALL operation uses a subquery which simply looks for
any row in the Tests table for the current customer where the Passed column
is TRUE. By using the EXISTS and NOT EXISTS predicates in the outer queries'
WHERE clauses, in the first part of the UNION ALL operation all rows are
counted where there is a row for that customer where Passed = TRUE, and in
the in the second part of the UNION ALL operation all rows are returned
where there is no row for that customer where Passed = TRUE. In the latter
case the second column is NULL instead of returning a count.


Ken Sheridan
Stafford, England
 
Back
Top