Query to show current issue of records

[justmegan93]

HELP!

I have a table which records data for documents within the company

I have columns within the table which includes reference no., title, issue no. and date

As the documents are update the tables will include multiple entries of the same ref no although will have a new issue number each time

I am looking to create a query which will display only records with a unique reference number although the most recent (highest) issue no.



Hope this makes sense
Thanks for any help!

[ranman256]

make a query to pull only 2 fields, REF#, ISSUE#
turn on summation totals,
under ISSUE#, set to MAX.

this will pull the most recent issue for each Ref#.

[orange]

As the documents are update the tables will include multiple entries of the same ref no although will have a new issue number each time

It sounds like you may have a design issue, but it depends on your requirements.
If a Document can have 0,1 or many References, then you have a 1 to Many relationship between tables. This would be part of a normalized design. And you could always find the latest assigned reference number without losing other reference data.
But if the requirement is to only record the latest reference, then overwriting the current values could work (and remove previous/history record/info).

[justmegan93]

It sounds like you may have a design issue, but it depends on your requirements.
If a Document can have 0,1 or many References, then you have a 1 to Many relationship between tables. This would be part of a normalized design. And you could always find the latest assigned reference number without losing other reference data.
But if the requirement is to only record the latest reference, then overwriting the current values could work (and remove previous/history record/info).

Each document will have the same reference no. although there can be many issues per reference
e.g.
Ref01 Issue01
Ref01 Issue02
Ref01 Issue03
Ref02 Issue01
Ref02 Issue02

I am ultimately trying to create a form which shows only the most current issue for each reference

[orange]

So let's go back to analysis and design of the "business problem/opportunity".

You have Documents, Reference numbers and Issues.
if a reference number could have Many Issues, then for any Document you need to identify which Reference(s) and which Issues apply.

Document--->DocumentRef<-------Ref
           AndIssue<------------Issue

where table DocRefAndIssue could contain:
Doc1  Ref100  Issue 29
Doc1  Ref100  Issue 30
Doc1  Ref100  Issue 34
Doc2  Ref100  Issue 31
Doc2  Ref101  Issue 31