Determining a Status Based on multiple rows in a query

[chammock]

I have a Group By query that groups records based on whether the Result
is Pass or Fail for a test. So for each item there could be 1 or 2 rows
in my query with more than one result for Pass or Fail. So I have a
Count on the Pass or Fail (ResultDescription) field.

My need is to assign a status based on how many Pass and Fail records I
have for each item. There can be up to 3 records for each item based on
the fact that 2 of 3 records must agree. If the first two tests are
Pass then the Status = Complete. If there is 1 Pass and 1 Fail, the
Status = Needs 3rd Test. If there are 2 Fail records, then Status =
Repair.

Hopefully you can see where I am going. I just cannot get my head
wrapped around the best way to aggregate the multiple records into a
Status. Here is a sample of the data.

ID Number ResultDescription CountOfResultID
137 Q1 Pass 1
137 Q1 Fail 2

In this case, item Q1 has two Fail and 1 Pass so the status = Needs
Repair.

 

[Tom Ellison]

Dear Chammock:

The approach could be to create two queries, one that has the count of the
Pass and another that has the count of Fail. Create the sets so they both
have 4 columns:

ID Number
PassCount
FailCount

Use a column in the Pass query with the constant 0 for the value in the
FailCount, and the Fail query with a 0 in the PassCount column.

Next, create a UNION ALL of the two queries. Then use an aggregate query,
grouped on the ID Number and summing the PassCount and FailCount.

Something like this:

SELECT [ID Number], COUNT(*) AS PassCount, 0 AS FailCount
FROM YourTable
WHERE ResultDescription = "Pass"
GROUP BY [ID Number]
UNION ALL
SELECT [ID Number], 0 AS PassCount, COUNT(*) AS FailCount
FROM YourTable
WHERE ResultDescription = "Pass"
GROUP BY [ID Number]

I'll assume this is saved as Query1.

Based on this:

SELECT [ID Number], SUM(PassCount) AS PassCount,
SUM(FailCount) AS FailCount
FROM Query1
GROUP BY [ID Number]

Does this help?

Tom Ellison

 

[Michel Walsh]

Hi,


SELECT IdNumber,
SUM(iif(result="pass", 1, 0)) AS numberOfPass,
SUM(iif(result="fail", 1, 0)) AS numberOfFail,
SWITCH( numberOfPass>=2, "Pass",
numberOfPass=1 AND numberOfFail=1, "Need more test",
numberOfFail>=2, "Repair",
true, "Need more test") As action
FROM myTable
GROUP BY IdNumber


I assume it is self explanatory, except maybe for the last case of the
SWITH: true catch all other cases, including if you have just ONE test done
on the "idNumber", so I imagine the solution is to require more test on it.


Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Make this your first query.

QUERY – Pass-Fail
SELECT [Pass or Fail].ID, [Pass or Fail].Number,
Sum(IIf([ResultDescription]="Pass",1,0)) AS Pass,
Sum(IIf([ResultDescription]="Fail",1,0)) AS Fail
FROM [Pass or Fail]
GROUP BY [Pass or Fail].ID, [Pass or Fail].Number;

Then this one.

SELECT [Pass-fail].ID, [Pass-fail].Number,
IIf([Pass]=2,"Passed",IIf([Fail]=2,"Failed",IIf([Pass]+[Fail]<3,"More
test","Error"))) AS Results
FROM [Pass-fail];

 

[chammock]

This worked well after I ironed it all out. Thanks so much. I have
verified that the query is returning the correct number of rows and the
correct data by checking it against the source. Here is a sample of the
data. The far right Status column is what I am after. This Status
column is not ACTUAL data, but what it should be if I could get my
logic to work in assigning a status.

FastenerID SumOfPassCount SumOfFailCount THE STATUS
111 1 0 Incomplete
4 1 1 Requires Level 3 Inspection
5 0 1 Incomplete
6 2 1 Complete
8 2 0 Complete
138 1 2 Requires Repair
139 0 2 Requires Repair

Now I am trying to assign a status based on the values in the Sum
columns. Here is my criteria. In general it requires two Passes to be
complete. Two Fails means a repair is in order. 1 Pass and 1 Fail means
a higher level inspector needs to do the third inspection. Less than 2
inspections is Incomplete status.

# Pass # Fail Status
0 0 Incomplete
1 0 Incomplete
0 1 Incomplete
1 1 Requires Level 3 Inspection
2 0 Complete
0 2 Requires Repair


I have tried the following. The Pass=2 and Fail=2 work, but it seems to
fail on the Fail=1 and Pass=1 logic in this part >> IIf(([FailCount]=1
And [PassCount]=1), ...

Status: IIf([PassCount]=2,"Passed 2 Inspections -
Complete",IIf([FailCount]=2,"Failed 2 Inspections - Needs
Repair",IIf(([FailCount]=1 And [PassCount]=1),"1 Pass and 1 Fail
Inspection - Requires Level 3 Inspection","Less Than 2 Inspections
Performed - Incomplete")))

Is there a better way to assign the status?

 

[chammock]

For some reason I had not seen the two additional posts. I added the
Switch statement, tweaked it a little and it seems to be working.
Thanks again.

 

[Tom Ellison]

Dear Chammock:

Working with your code:

IIf([PassCount] = 2,"Passed 2 Inspections - Complete",
IIf([FailCount] = 2,"Failed 2 Inspections - Needs Repair",
IIf(([FailCount] = 1 And [PassCount] = 1),"1 Pass and 1 Fail Inspection -
Requires Level 3 Inspection",
"Less Than 2 Inspections Performed - Incomplete")))

Now, when [FailCount]=1 And [PassCount]=1 what does this return? What do
you want it to return? The Level 3 message, right?

You show cases where more than 2 passes are included, being FastenerID 6 and
138. I don't know how high this goes, but if you had 5 tests, with 2 pass
and 3 fail, your logic would show "Complete". Is this what you intend?

Tom Ellison