Query to take count and find most likely

[Jordan]

We are trying to build a query to diagmose our products by the tests they
fail in our automatic testing machine. Engineers will be able to look at
symptom X, Y, Z and know since it does not also have symptom A, B, or C then
it is problem 1. Our basic techs cannot tell so we want to create a
database with tables that take symptoms and come to a conclusion or
conclusions about what is or may be the problem. Kind of like on House
where they always say can be this because of all these symptoms, but not
that because they don't have another symptom.

Right now Engineers of the product have a very very bad idea on how to do it
(By bad I mean stupid). They want to create a table with all the symptoms
and assign each symptom a code (OK), but then they want to create a table
that has the end diagnosis and a string that has all the codes that would
end in that diagnosis. For example if the product failed with codes, 1001,
1002, and 1003 that would mean the diagnosis would be ABCD so the want the
data in the fields to be:

FailureCodes Diagnosis
1001 + 1002 + 1003 ABCD
1002 + 1005 + 1007 BCDL
1004 + 1005 + 1010 EFLM
1004 + 1005 + 1010 WXYZ

Can you see how what a huge nightmare that will be to take all the results
from one table and try to match them up to some form of parsed string in
another table to come up with the end diagnosis. I know it would be best to
setup a table so like so:

1001 ABCD
1002 ABCD
1002 BCDL
1003 ABCD
1004 EFLM
1004 WZYZ
1005 BCDL
1005 EFLM
1005 WXYZ
1007 BCDL
1010 EFLM
1010 WXYZ

So the question is if I setup the table like this as it should be how would
I query the table to:

1. Come to the result that 1001, 1002 and 1003 results in ABCD
2. 1004, 1005, and 1010 result in both ELFM and WXYZ

 

[Klatuu]

Your's is the better design. Their design would be very difficult to query.
For yours, all you would need is:
SELECT DISTINCT Diagnosis FROM tblDiagnostics WHERE FailureCode IN(1004,
1005,1010);

The results would be 3 rows:

EFLM
BCDL
WXYZ

 

[John Spencer]

I would think that the query would be more like the following; Since all
three failure codes would be needed to identify the diagnosis

SELECT Diagnosis
FROM tblDiagnostics
WHERE FailureCode IN (1004,
1005,1010)
GROUP BY Diagnosis
HAVING COUNT(*)=3


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Klatuu]

I see your point, but there is no assurance the diagnosis would be in all 3
failure codes. I think, perhaps counting the occurances of each diagnosis
might be a better solution.

SELECT Count(Diagnosis) As DiagCount
FROM tblDiagnostics
WHERE FailureCode IN (1004,1005,1010)
GROUP BY Count(Diagnosis)
ORDER BY Count(Diagnosis) DESCENDING

This, I think, would give probabilities from most to least likely.

 

[Jordan]

Thank you. All three have give me an idea.

Some diagnosis would have maybe 3 out of 3, or 4 out of 5 or so. Some
diagnosis may have all of the problems I am querying, but maybe a few more
(4 sought, but diagnosis needs 6 symptoms). What I should do is either
maintain a table of the count of symptoms or run an additional query on the
diagsnosis to find the total count of symptoms to make up each of the
resulting diagnostic possibilities. That way I can do a percentage so my
results would be something like:

DiagnosisA 100 (4 out of 4)
DiagnosisB 75 (3 out of 4)
DiagnosisC 50 (2 out of 4)
DiagnosisD 150 (6 symptoms make up the diage but only 4 sought)

In the same query instead of showing 100, 75, 50, and 150 I would do an IF
statement so that results not = 100% matching would be 100 - the absolute
difference. This way 75% and 125% would be even..