Counting unique records and unique with condition (1 Viewer)

Magnus1982

Registered User.
Local time
Today, 04:40
Joined
Apr 29, 2017
Messages
41
During my work with access I stock because of some problem. Looking help with this.
Let's say I have query (qryStat) which contain two columns:

Color Result

RED FAIL

RED PASS

BLUE FAIL

BLUE FAIL

BLUE PASS

GREEN FAIL

GREEN FAIL

PINK PASS

In the form (fmFinal) I have text box (txtBox1,tztBox2)
What I want to achieve:

1. txtBox1 - count unique value from Color column (in this case = 4)
2. txtBox2 - count colours which have Result PASS and don't have result FAIL (in this case = 1)

I try to do that with Dcount but I was able only to count all which have PASS or ALL which have FAIL

Can any one explain me how to do that?
 

plog

Banishment Pending
Local time
Today, 06:40
Joined
May 11, 2011
Messages
11,611
I would build an aggregate query on qryStat with a special column to determine ifit has a FAIL:

Code:
SELECT Color, SUM(Iif(Result='FAIL', 1,0) AS Fails
FROM qryStat
GROUP BY Color

That will give you a record for each unique color and denotes how many fails each has.
That will be the query that feeds your 2 text boxes. You will set their control sources to DCount functions (https://www.techonthenet.com/access/functions/domain/dcount.php).

txtBox1 will be a simple DCount using just the first 2 arguments.
txtBox2 will require you to use criteria to exclude those with a Fails field greater than 0

Give the above shot and post back any issues you encounter.
 

Magnus1982

Registered User.
Local time
Today, 04:40
Joined
Apr 29, 2017
Messages
41
Thank you . Perfect solution :) Once again tnx
 

Users who are viewing this thread

Top Bottom