Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version


OK, here is what I see. You are treating a relational database as if it were a flat file. That is, everything is grouped into one long record and space is allocated for data even when it is not going to be used. Two or even three tables should be used here. Possibly 1) Company/Location; 2) Visit information; 3) Test Collection/Results. Especially in #3, tests would occupy only what is needed. It would be flexible allowing 1 to X tests to be done.

Instead of all the individual fields on the form, some could be put in table controls saving space and allowing flexible number of results.

This form is for display purposes only. Input must be on another form. There are many reasons for this with some being the Query where the data comes from & that some data is calculated. To input data & display calculated data will require a lot of changes with possibly some of the table changes mentioned previously.

Now for some actual results. First you are selecting a couple of fields twice - "TP2" & "OBAF2". Doesn't create a problem or errors but is unnecessary and can add confusion. Your Pass/Fail result can be determined using a 'CASE WHEN' statement. But this requires the result of the calculation be done already or duplicating this calculation. In choosing the former, see the result in posted sample in Query3. This places all your info in a second select - Select From Select - where the Case statement can access the calculated results.

The sample has your form modified only in the fact in now uses Query3 instead of Query1. Result was provided for left section only and record #2 was deleted from table as it was empty. Placed data in 'AllowRepeatErr' field to get test results.

Sample - Measurements.odb