Query to find last and second last entries (All)

Topic

New Reply

I have a history table with HistoryID (auton), HistoryDate and HistoryAmount. Each time a customer’s payment changes (usually annually) I add a new entry for each customer.

For a billing report I need to establish the difference between the latest amount and the previous (second last) amount so that I can work with the difference.

I can get to the latest amount by using an aggregate function (last) in the query, but I don’t know how to get at the previous amount. Can anyone help?

Reply | Quote

Replies

Hi David,

You can probably use the second method (“Using code”) described in ACC2000: Referring to a Field in the Previous Record or Next Record.

Note: this will be slow if you have a large number of records.

Reply | Quote

Hi David,

You can probably use the second method (“Using code”) described in ACC2000: Referring to a Field in the Previous Record or Next Record.

Note: this will be slow if you have a large number of records.

Reply | Quote

Actually, you need to be careful. Using the Last function may give you the “last” amount, but that may not coincide with the latest date. You could do this with about 3 queries, but it would be messy and would involve subqueries as well.

Reply | Quote

Can you not just run a simple query along the lines of:

Create Table TempHistoryRecords
Select Top 2 CustomerID, HistoryID, HistoryDate, HistoryAmount
From tblRecords
Where CustomerID in [Enter Customer ID]
OrderBy HistoryID DESC;

This would then return the last two records for a specified customer (change the where clause to make it more user friendly) which could then be interrogated using vba etc to move the two entries into a new table in a single row (i.e. transposing the record)…

…or am I missing a trick

Kind regards

Reply | Quote

Can you not just run a simple query along the lines of:

Create Table TempHistoryRecords
Select Top 2 CustomerID, HistoryID, HistoryDate, HistoryAmount
From tblRecords
Where CustomerID in [Enter Customer ID]
OrderBy HistoryID DESC;

This would then return the last two records for a specified customer (change the where clause to make it more user friendly) which could then be interrogated using vba etc to move the two entries into a new table in a single row (i.e. transposing the record)…

…or am I missing a trick

Kind regards

Reply | Quote

Thank you all for your comments. It looks like doing this by a query is out of the question. I’ve started to work up some code that will take Chris’s suggested “top 2” as the SQL source of a recordset and then do the calculations in code, outputting the result to a temporary table.

Charlotte, you are absolutely right. The search can’t just be on the ID as this may be out of order. I solved this dilemma in a different query and the answer was to sort by date order.

Agani many thanks

Reply | Quote

Thank you all for your comments. It looks like doing this by a query is out of the question. I’ve started to work up some code that will take Chris’s suggested “top 2” as the SQL source of a recordset and then do the calculations in code, outputting the result to a temporary table.

Charlotte, you are absolutely right. The search can’t just be on the ID as this may be out of order. I solved this dilemma in a different query and the answer was to sort by date order.

Agani many thanks

Reply | Quote

Actually, you need to be careful. Using the Last function may give you the “last” amount, but that may not coincide with the latest date. You could do this with about 3 queries, but it would be messy and would involve subqueries as well.

Reply | Quote