Comparing test results with standards 1

[InfoNow]

Please help.

I am trying to compare the test results table to the specs table and have it print out a report showing the pass/no pass. For example, I did a weight test on a partnumber of 123456 and the results was 13.5mg. The specification for partnumber 123456 is a range of 12mg to 15mg seconds. If the results is within this range it passed and if not it fail. I have two tables setup, one is the specs and the other is the test results. How do I go about showing this in the forms and the reports. Also note that the partnumber will be tested several times so it is not a unique number.

Thank,
Quang

 

[tlbroadbent]

Here is a simple query to compare test results with specs and provide the pass/fail status.

Select a.PartNumber, a.TestResult,
IIf(a.TestResult Between b.LowVal
And b.HighVal, "Pass", "Fail&quot
From TestTable a Inner Join SpecsTable b
On a.PartNumber = b.PartNumber

You can use this query as the source for a report. You will likely want to add more information to the query but this should give you a good start. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[InfoNow]

Terry,
Thank you for responding. Instead of doing it in a query, I did it from a text box using IIF function. That seems to work ok, but it only displays the results in the form. How do I go about having it saves the results to the test table?

TIA,
Quang

 

[tlbroadbent]

Typically, I would not save the "Pass/Fail" code to the test table because the code depends on the test result not on the key of the table. "Pass/Fail" is a derived value that you can derive any time you query the table.

However, if you want to store the value in the table just use the IIf in an update query as follows.

UPDATE PartTbl INNER JOIN TestTbl
ON PartTbl.PartNumber = TestTbl.PartNumber
SET TestTbl.PassFail =
IIf([TestTbl].[TestResult] Between [PartTbl].[LowVal]
And [PartTbl].[HighVal],"Pass","Fail"

Better yet, I would make PassFail a Yes/No datatype and use this query to update it.

UPDATE PartTbl INNER JOIN TestTbl
ON PartTbl.PartNumber = TestTbl.PartNumber
SET TestTbl.PassFail =
IIf([TestTbl].[TestResult] Between [PartTbl].[LowVal]
And [PartTbl].[HighVal],Yes,No); Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[InfoNow]

Terry,

Thank you, I took your advice and go with not storing the code in the table. It seems to be working the way I wanted now. Thanks again for your help.

Quang