I have Columns that contain


{Document Name} | {VERSION} | {DATE} | {CLIENT DOC #} | {RELEASED BY} | {CHRONICLE_ID}


And lots more


What Is happening is There are many duplicate document names that are different because of their Version (Eg. there's versions 1A 1B 1B 1C 1C 2A 3 4D 4D 5E 5F 5D 6 7 8A 8A 8B 8C 8C)

but the chroicle ID is different for each Number change

So the 1A B C have a chronicle id of 1
2A has a chronicle id of 2
3 chronicle id of 3


So what I have to do is get the highest Version per Document Name which should be simple as I should just group by chronicle id and take the Max value in version

problem is that I need to have all those extra fields such as date, document name, client doc #, released by etc etc and if you do a group by query and then rejoin it to the original table I end up with duplicates and then some because there are many fields that are different and I can't add them to the group by query(or else I wouldn't be asking this)

Did you try this?

This may be a roundabout way, but it may work.

1) Take the original table, go into design view and add a autonumber field.
2) Use the Dups Wizard and only pull in the Doc Name as the dup field and the Chronicle ID and AutoNumber field. Choose Modify Design
3) Add the Group By to Doc Name and choose max for Chronicle ID and AutoNumberField. Make a table out of this.
4) Build another query joining the two tables on the AutoNumber and Doc Name Field. Then pull all the data from the original table

It may be a better way, but hopefully this works.