MS Access query - finding the reocrd with a maximum value

January 23, 2007 1:26 PM

In MsAccess, how can I choose the record where a certain value is maximum or minimum?

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.

posted by buildmyworld to Computers & Internet (3 answers total)

Best answer: You can do it in two steps: first, a make-table query that groups by Item Number and gives Max of Task Start Date. Call that output Table A.

Then do a select query that links (inner join) Table A to your original table by Item Number-Item Number AND Max of Start Date- Start Date.
posted by COBRA! at 1:41 PM on January 23, 2007

Response by poster: Thank you! How have I used Access for a decade without knowing about make-table queries?
posted by buildmyworld at 2:09 PM on January 23, 2007

heh. Just get a job at a low-budget nonprofit that forces you to do all of your DB work in Access, and you'll be shocked at the things you're forced to learn.

Glad to help...
posted by COBRA! at 2:18 PM on January 23, 2007