Returning Difference of Count in Same Table

[jmltinc]

Hi Folks,



I have a single table containing parent test data and of the various fields are: ATE_PK (Primary Key), ATE_SERIAL (serial number), ATE_TIME_FINISHED (time test was completed), and ATE_STATUS (0=FAIL, 1=PASS).

I want to get a First Pass Yield by dividing those units that failed on the first try (MIN(ATE_TIME_FINISHED)) and ATE_STATUS=0)
by
all units tested on first try (MIN(ATE_TIME_FINISHED)), grouping by ATE_SERIAL.

Can anyone help with this query?

Thanks,
-John

[ranman256]

You'll need a few queries,
Q1: ID, min(date) ,where status=0
Q2: Id, min(date)

now you have the min dates,do the counts:
Q3: join Q1 to the data via ID and get the count
Q4: join Q2 to the data via ID ,get count

Q5: using Q3 and Q4 to get percents.

[jmltinc]

You'll need a few queries,
Q1: ID, min(date) ,where status=0
Q2: Id, min(date)

now you have the min dates,do the counts:
Q3: join Q1 to the data via ID and get the count
Q4: join Q2 to the data via ID ,get count

Q5: using Q3 and Q4 to get percents.

Thank you for your input. I can do that with VBA inside an event, but am really hoping for an inline query for a control...

-J

[aytee111]

What Ranman suggested would entail using query Q5, no VBA at all.

[jmltinc]

What Ranman suggested would entail using query Q5, no VBA at all.

I'm sorry, but I do not understand.

Q1 and Q2 return as expected. Joining Q1 (and I went no further) returns 2 records - not one:

Data:

ATE_PK	ATE_SEQUENCE_FK	ATE_SERIAL	ATE_STATION	ATE_STATUS	ATE_START_TIME	ATE_END_TIME
7	10	161245837	13	0	12/11/2015	12/11/2016 3:13:03 PM
8	10	161245837	13	0	12/12/2015	12/12/2016 3:13:03 PM
9	10	161245847	13	1	12/12/2016 10:52:21 AM	12/12/2016 10:52:21 AM
10	10	161245840	13	1	12/12/2016 11:24:01 AM	12/12/2016 11:24:01 AM
11	10	161245839	13	1	12/12/2016 11:53:12 AM	12/12/2016 11:53:12 AM
15	10	161245838	13	1	12/12/2016 1:31:13 PM	12/12/2016 1:31:13 PM
16	10	161245834	13	1	12/12/2016 2:01:59 PM	12/12/2016 2:01:59 PM
17	10	161245835	13	1	12/12/2016 2:27:04 PM	12/12/2016 2:27:04 PM
18	10	161245837	13	1	12/12/2016 3:13:03 PM	12/12/2016 3:13:03 PM
19	10	161245844	13	1	12/12/2016 3:36:30 PM	12/12/2016 3:36:30 PM

SQL:
SELECT ATE_ATE_TESTS.ATE_SERIAL, Min(ATE_ATE_TESTS.ATE_END_TIME) AS MinOfATE_END_TIME, Count(ATE_ATE_TESTS_1.ATE_PK) AS CountOfATE_PK
FROM ATE_ATE_TESTS INNER JOIN ATE_ATE_TESTS AS ATE_ATE_TESTS_1 ON ATE_ATE_TESTS.ATE_PK = ATE_ATE_TESTS_1.ATE_PK
WHERE (((ATE_ATE_TESTS.ATE_STATUS)=0))
GROUP BY ATE_ATE_TESTS.ATE_SERIAL;

Return:

ATE_SERIAL	MinOfATE_END_TIME	CountOfATE_PK
161245837	12/11/2016 3:13:03 PM	2

What am I missing here???

Thanks,
-John

[aytee111]

Q1: get all records
SELECT Table2.sn, Min(Table2.Time) AS MinOfTime FROM Table2 WHERE (((Table2.time) Is Not Null)) GROUP BY Table2.sn;

Q2: get failed
SELECT Table2.sn, Table2.Time FROM q1 INNER JOIN Table2 ON (q1.MinOfTime = Table2.time) AND (q1.sn = Table2.sn) WHERE (((Table2.pf)=False));

Q3: get count of fails
SELECT Count(q1.sn) AS CountOfsn FROM q1;

Q4: get count of all
SELECT Count(q2.sn) AS CountOfsn FROM q2;

Q5: get yield
SELECT q4.CountOfsn / q3.CountOfsn * 100 AS yield FROM q3, q4;