Filter Subform Records

[Anand]

Hi,
I am using A2k. I have two tables tblPO and tblMaterials.

Records in tblMaterials have a revision number. tblMaterials has three
primary keys - MaterialID, POID and RevNo. When the user amends a record in
tblMaterials a new record gets created with the amended information. In the
new record Field RevNo gets incremented by 1 while POID and MaterialID remain
same.

Two questions:
Is it a good idea to store revisions in the same table?

When displayed as a subform with tblPOID, how do I get Access to display the
tblMaterials records with the latest revision number only. Not all the
related records with the same POID?

Eg. There are two records in tblMaterials
1) POID = 1, MaterialID = 1, RevNo= 0
2) POID = 1, MaterialID = 1, RevNo = 1
Access must display only record #2 in the subform. Any ideas?

TIA
Anand

 

[Paul]

Create an agregate query (Epsilon symbol on the query toolbar) that returns
MaterialID, POID and RevNo. Set MaterialID and POID to 'Group By', set RevNo
to 'Max'.

This query returns the highest RevNo for each materialID - POID combination.

Create a new query that joins the query above to tblMaterials and create
joins on MaterialID, POID and RevNo. Select whatever output fields you need.
Only the latest RevNo records will then be displayed.