finding max per group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony - 13 Jun 2005 22:48 GMT

For my report I need to list the most recent item per group. I have the query
listing machine, projects machine is used for, date for the project and some
details for each project.
To simplify say I have the following fields: machine, project, date,
location. machine comes from tblMachine which holds machine details. other
fields come from tblProjects which holds all project related details. Those
tables are linked by machineserialnumber.
For each machine I need to list the most recent project, showing machine,
project, date and location. I can do it by using nested queries but I have
seen advice in the book that SQL query will give me better performance. What
is the SQL code I need to use to do what I need.

Thanks for help.

Tony

Reply to this Message

Marshall Barton - 14 Jun 2005 00:46 GMT

>For my report I need to list the most recent item per group. I have the query
>listing machine, projects machine is used for, date for the project and some
[quoted text clipped - 7 lines]
>seen advice in the book that SQL query will give me better performance. What
>is the SQL code I need to use to do what I need.

Note that a query IS an SQL statement, regardless of how you
create it (just switch the query to SQL view to see it.

I'm not sure what you mean by "nested" queries. In my
lexicon, that means you have two (or more) SELECTs in a
single query, which is fine. Maybe you mean you have one
query based on another query, which is also fine. I
wouldn't worry about it unless you are experiencing an
unacceptable delay, in which case making sure you have good
indexing will provide the greatest benefit.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Walter Steadman - 14 Jun 2005 03:18 GMT

Tony,
I think I understand what you are asking, and you can get the max result
from each group in a query by doing a Total Query.

Add all the fields you need to see into a query grid. Select the sum button
so that you get the total line in the grid, then under date instead of
selecting GroupBy you would select Max.
This worked for me in a similar type of query. Thanks to the help from some
folks out here in the groups

HTH
Wally

> For my report I need to list the most recent item per group. I have the
> query
[quoted text clipped - 15 lines]
>
> Tony

Reply to this Message