Select record with highest value within a query ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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' ?
 
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:
Click to expand...
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
 
Hello Nikos

We now have it working with multiple tables.

Thank you very much for your prompt help.

Regards
Glynn Taylor
 
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
 
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
 
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
 
Back
Top