J
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
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