Counting unique records and unique with condition

[Magnus1982]

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]

I would build an aggregate query on qryStat with a special column to determine ifit has a FAIL:

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]

Thank you . Perfect solution Once again tnx