finding max per group

[Guest]

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

 

[Marshall Barton]

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.

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.

 

[Walter Steadman]

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