Last Grouping in My Query Stopped Working

Question

Hi,

I have an RMA database built in Access that my client has been using since January 2013. There is a query that is to pull the last ship date and sales order number for a customers part. It has always worked until the last couple of weeks.

This is the query with the last two fields, Ship Date (BKAR_INVL_ASD) and Sales Order Number (BKAR_INV_SONUM) grouped and sorted to show the last record that should be shown for a Customer Code (BKAR_INV_CUSCOD) and for the Part (BKAR_INVL_PCODE) when the Last Grouping is applied:

This is the results of this query. The last record in this grouping for BKAR_INV_CUSCOD: 000132 and BKAR_INVL_PCODE: 65784 is 9/19/2022 with SO Number 88941. It is highlighted in blue.

Now I will apply the Last grouping to this query as shown below:

Here is the results:

Notice it is not showing the last record but the record before the last.

If I understand correctly the Last Grouping is to give you the Last Record of a grouping result. Why is this not working?

Thanks You,

Priscilla Pollock

Answer 1

Are you attempting to return the most recently dated row per BKAR_INVL_PCODE per BKAR_INV_CUSCOD? If so you misunderstand the meaning of the LAST operator. It will not return the latest date. For that you must use the MAX operator. Moreover, you cannot simply GROUP BY other columns if, in addition to the aggregated value, you wish to return data from columns other than those by which the grouping is applied. To do so you must restrict the results of the query by a subquery in which the MAX value is returned, and which is correlated with the outer query by giving the two instances of the table in question different aliases. The following is a simple example using a Transactions table, from which the most recent transaction per customer is returned:

SELECT CustomerID, TransactionDate, TransactionAmount
FROM Transactions AS T1
WHERE TransactionDate =
(SELECT MAX (TransactionDate)
FROM Transactions AS T2
WHERE T2.CustomerID = T1.CustomerID)
ORDER BY CustomerID;

In this the subquery is correlated with the outer query on CustomerID and thus restricts the rows returned to those with the MAX TransactionDate per customer. A simple aggregating query grouped by CustomerID cannot be used for this because the TransactionAmount column is not one by which the query is grouped.

The table used by this query comes from the DatabaseBasics demo in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

in which you can try it for self.

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.