Last update record display on the report

[Guest]

I have a table contains a list of engineering drawings and documents with
their own revisions.
How to set up only latest revision record (drawings/documents) to shown on
Report/Forms. The Revision would have the the latest revision as follows:
0, 1, 2, 3,.. means the bigger value the latest is.
A, B, C,..
A1, A2, A3..
Say a subcontractor needs to check with me of the latest revision that has
been issued to them, I should be able to show them a report lists the latest
or specific revision of document issue to them.
I would also like to know how to update a new revision to a document instead
of copying the same document and change the revision.
Thanks guys.

 

[Guest]

Use a summation query and set the Total row for the revision field to Max.

Are you using a separate record for each sheet? Like --
Draw# SH Rev Title
DWG111-EEE-01 1 A Floor Plan
DWG111-EEE-01 2 C 2nd Floor Electric Panels

 

[Guest]

If the values of other columns in the table change per revision and not just
the Revision column's value then you'll need to use a correlated subquery in
the outer query's WHERE clause, e.g.

SELECT D1.*
FROM Drawings As D1
WHERE D1.Revision =
(SELECT MAX(D2.Revision)
FROM Drawings AS D2
WHERE D2.Drawing = D1.Drawing);

To avoid having to duplicate data when entering a new revision you should
decompose your table so that you have one table, e.g. Drawings, with columns
representing those attributes which don't change per revision, and another
table Revisions with columns representing those attributes which do change
per revision. Include a foreign key column in the Revisions table which
references the primary key of Drawings. There would thus be a one-to-many
relationship from Drawings to Revisions. Decomposing a table like this not
only avoids repetitive data entry, but it safeguards your database against
update anomalies by eliminating redundancy. This process is known as
normalization in the jargon of the relational database model.