MS Access query - finding the reocrd with a maximum value
January 23, 2007 1:26 PM Subscribe
In MsAccess, how can I choose the record where a certain value is maximum or minimum?
posted by buildmyworld to Computers & Internet (3 answers total)
I have multiple rows for each item. Each row includes columns such as: item number, version number, person, task start date, task end date, task result. A given item could have any number of tasks.
I want to run a query that returns one row per item and includes all the information about the last task for that item. In other words, this will be all the same columns as the original table, but only rows where the task start date is maximum for that item number.
My first try was to find the maximum date for each item number and then use that to select the row. But I can't join that back up to the original data because it's a circular reference, so I can't find out the other information about that task.
Any suggestions? I'm guessing this would need to be done as a sql query, but I'm having no luck with Access help or google in finding a starting point.
I'm using Access rather than Excel because I need to be able to drop in different record sets and get identically formatted reports out the other side. I've done this in Excel before by hand, but I can't scale that up for the number of different reports needed.
Technical specs: Access 2003 on Windows XP
Thank you oh all-knowing hive mind.