access query with duplicates data

[touchmove]

Hi to everyone!

Below is my data:

4	KAP2C1-35-AR-0400-DD-0100	0	4/20/2015	5/1/2015	5/14/2015	Revise and Resubmit	0
5	KAP2C1-35-AR-0400-DD-0100	1		5/21/2015	5/27/2015	Approved As Noted	0
6	KAP2C1-35-AR-0400-DD-0100	2		6/1/2015	6/7/2015	Approved	0

<caption> REVIEW CYCLE </caption><thead>
[TH="bgcolor: #c0c0c0"] Detail [/TH]
[TH="bgcolor: #c0c0c0"] DrawingNo [/TH]
[TH="bgcolor: #c0c0c0"] RevisionNo [/TH]
[TH="bgcolor: #c0c0c0"] Advance Copy [/TH]
[TH="bgcolor: #c0c0c0"] Official Submittal [/TH]
[TH="bgcolor: #c0c0c0"] Date Commented [/TH]
[TH="bgcolor: #c0c0c0"] Status [/TH]
[TH="bgcolor: #c0c0c0"] ATTACHMENTS [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

As you can see, there is duplicates in DrawingNo column. I want to create a query which would get only the DrawingNo with the latest RevisionNo which is RevisionNo 2.


Can someone help me with the code?

Appreciate your quick reply.


More powers!

 

[Trevor G]

Create a query and add your fields then select the Totals Icon this should Group things, change the group for the Revision Number to Max that should give you what you need.

 

[touchmove]

KAP2C1-35-AR-0400-DD-0100	1		5/21/2015	5/27/2015	Approved As Noted	0
KAP2C1-35-AR-0400-DD-0100	2		6/1/2015	6/7/2015	Approved	0
KAP2C1-35-AR-0400-DD-0100	0	4/20/2015	5/1/2015	5/14/2015	Revise and Resubmit	0

<caption> REVIEW CYCLE Query </caption><thead>
[TH="bgcolor: #c0c0c0"] DrawingNo [/TH]
[TH="bgcolor: #c0c0c0"] MaxOfRevisionNo [/TH]
[TH="bgcolor: #c0c0c0"] Advance Copy [/TH]
[TH="bgcolor: #c0c0c0"] Official Submittal [/TH]
[TH="bgcolor: #c0c0c0"] Date Commented [/TH]
[TH="bgcolor: #c0c0c0"] Status [/TH]
[TH="bgcolor: #c0c0c0"] ATTACHMENTS [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Trevor,

Above table is the result after following your procedure. How can I display only DrawingNo with the latest revision?

 

[Joe4]

You can do this in a set of two queries.

First, create a query called "LatestRevision".
Do this by adding the table to the query, and adding only the "DrawingNo" and "RevisionNo" fields to the query (but NO other fields).
Then, click on the Totals button.
Under the "DrawingNo" field, leave the Totals row setting to "Group By", but under the "RevisionNo" field, change the value to "Max".
If you view the results, it will show you the latest RevisionNo for each DrawingNo.

Now, if you want to get all of the other field values, create a new query linking the ""LatestRevision" query you just made to your original table on BOTH fields in your query.
Then, select all of the fields you would like to return from the original table.

This should give you what you want.