SQL query for last transaction on MS Access

Question

Hi I have a table with the following fields:

Customerid
Customerpaydate
Amount

I need help in retrieving most recent transaction but no repetition in of customer ID.

I would appreciate your help

but i dont want customer id to be repeated either. I tried Distinct but id's are repeated — Muhammad Tabish, Nov 26 '19 at 20:23
A window function would work in SQL Server. Check out MS Access equivalents for example - https://stackoverflow.com/questions/18753368/row-number-in-access-select-statement — Joe C, Nov 26 '19 at 20:27

score 1 · Accepted Answer · answered Nov 26 '19 at 20:34

1

To pull out the most recent transaction par customer, a correlated subquery should get the job done:

select t.*
from mytable t
where customerpaydate = (
    select max(Customerpaydate) from mytable t1 where t1.customerid = t.customerid      
)

answered Nov 26 '19 at 20:34

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Nov 26 '19 at 20:30

This will give you the max paydate

 SELECT MAX(customerpaydate) as d 
 FROM table

This will give you the max paydate by customer

 SELECT customerid, max(customerpaydate) as d 
 FROM table 
 GROUP BY customerid

The will give you the most recent transaction

 SELECT *
 FROM TABLE
 where customerpaydate = (SELECT MAX(customerpaydate) as d FROM table)

This will give you the most recent transaction of each customer

 SELECT *
 FROM TABLE
 JOIN (
    SELECT customerid, max(customerpaydate) as d 
    FROM TABLE 
    GROUP BY customerid
 ) AS S ON S.customerid = TABLE.customerid and S.d = TABLE.customerpaydate

SQL query for last transaction on MS Access

2 Answers2