Show the rest of the fields for the Max record

[HomeALone]

I am using a query to group an ID# field (multiple ID# allowed), and select Max to show only the last record of each group. This is working OK.

I would like to show the rest of the fields that related to this "Max" record. If I just choose the fields, the query return all the records, not only the "Max" one from each group. How can I avoid this? Please advice!

 

[zambezibill]

Set the totals criteria of the other field(s) to MAX as well.

My brain is a bit foggy, but I vaguely recall that there may be some potential problem with using the MAX function in your asort of situation. Perhaps someone else can clear the fog. THe preferred alternative is to sort the field in descending order and then use a SELECT TOP 1 SQL statement.

Cheers, Bill

 

[zambezibill]

Set the totals criteria of the other field(s) to MAX as well.

My brain is a bit foggy, but I vaguely recall that there may be some potential problem with using the MAX function in your asort of situation. Perhaps someone else can clear the fog. THe preferred alternative is to sort the field in descending order and then use a SELECT TOP 1 SQL statement.

Cheers, Bill

 

[krsherm]

You need to create two queries... the first one should only select the max of the id number and its logical GROUP BY field. The second query should link to the first query on the id field and this is how you can access the other fields. For example, if you wanted to show Customer information for a customers' max (last) order id.

Query1 (qryOrderMax):

"SELECT CustomerID, Max(OrderID) AS MaxOrderID FROM tblCustomerOrders WHERE CustomerID = 12"

Query2 (qryShowMaxInfo):

"SELECT tblCustomers.CompanyName, tblCustomers.CompanyAddress
FROM tblCustomers INNER JOIN qryOrderMax ON tblCustomers.OrderID = qryOrderMax.MaxOrderID"

When you run the query you will get the additional info that you are looking for.

Please note that my example is somewhat sloppy (it is not actual, and from a referential integrity standpoint it sucks. It is just to get you on the right path). Also, the criteria could be specified in the WHERE clause of the Query2.

Hope that helps.

Kevin

 

[HomeALone]

I took Kevin's approach. It worked. Thank you very much!

Homealone again!