Help with query

  • Thread starter Thread starter Den
  • Start date Start date
D

Den

I have a table (joined to another) to maintain record of the pieces changed
in an instrument. The table contains the following fields; InsID, Pos1, Pos
2…..Pos 6 and Date Installed. An example of the information in the table is
as follow:

InsID Pos 1 Pos 2 Pos 3 Pos 4 Pos 5 Pos 6 Date
Installed

1 QA2 QA5 QA6 QA8 QA9 QA3
01/01/09
1 N/A QA11 N/A N/A QA20 N/A
02/15/09
2 QA40 QA25 QA30 QA12 QA22 QA35 02/20/09
2 QA50 N/A N/A N/A N/A QA55
03/01/09

Where the information under Pos is a serial numbers and N/A means Not
Applicable because the piece in that position was not changed.

I need a query or any other form to report the pieces currently installed in
the instrument. An example of the report that I need is as follow:

InsID Pos 1 Pos 2 Pos 3 Pos 4 Pos 5 Pos 6
1 QA2 QA11 QA6 QA8 QA9 QA3
2 QA50 QA25 QA30 QA12 QA22 QA55
 
Your table structure is wrong. Let me ask you this: what happens to all your
queries, forms, and reports when someone decides that a Pos 7 is needed?
You'll have to redesign all of them.

Your table should look like:

PosID InsID Pos SerialNo DateInstalled
1 1 1 QA2 01/01/09
2 1 2 QA5 01/01/09
and so on

The PosID field would be an autonumber the the primary key field.

You could skip the N/As. Next you would create a query that might need a
subquery to find the max DateInstalled for each Pos.

After you got that query worrking, you could do create a crosstab query
based on the first query to see the results that you wish.
 
Back
Top