Select record with highest value within a query ?

[Guest]

We have a table that contains many records for any one given part number each
having an 'effective date' field.

Is it possible to select only the part number record with the latest
'effective date' ?

 

[Nikos Yannacopoulos]

Glynn,

Make a query on the table, and while in design view go to menu item View

Totals; notice the new line that appears in the grid, headed Total:

with default value "Group Bu" under each field; leave as is under part
number, change to "Max" under the effective date field.
Note: don't include any other table fields in the query, so you only get
one record per part number. If you need to add more fields, experiment
with the different opyions of Total functions to get what you want.

HTH,
Nikos

 

[Guest]

Hello Nikos

We now have it working with multiple tables.

Thank you very much for your prompt help.

Regards
Glynn Taylor

 

[Nikos Yannacopoulos]

Glynn,

How do you mean "multiple tables"? This is usually an indication of poor
design, bound to create more problems down the road!Care to elaborate?

Nikos

 

[Guest]

Nikos

Not all data in a relational database is held on 1 table!

Therefore, it is not uncommon to query data from more that 1 related table!

Glynn

 

[Nikos Yannacopoulos]

Glynn,

Thanks for sharing this

Your original question implied you are not very advanced with Access
yet, so "multiple tables" made me suspect you were making a common
design mistake among inexperienced users, namely splitting similar data
to several tables with the same structure... and was only trying to help.

Good luck,
Nikos