0

Following is a sample table

SrNo, Date, TypeOfTran, CustID, AmtDue, AmtRec
1, 01-Jan-2021, 0,      A,       102,       0
2, 01-Jan-2021, 0,      A,       120,       0
1, 02-Jan-2021, 0,      D,       140,       0
1, 02-Jan-2021, 1,      D,         0,     176
2, 02-Jan-2021, 1,      D,         0,     188
1, 05-Jan-2021, 0,      P,         0,     196
2, 05-Jan-2021, 1,      L,         0,     134

*TypeOfTran [Due = 0, Receipt = 1]

SrNo resets on change of date and on change of TyepOTran

I want to create a query which would return last record of every customer.

A customer can make purchases and/or makes payment any number of time during the day.

I read the answers given in how do I query sql for a latest record date for each user query but that is based on one condition that is date.

I am using MS Access

Thank you.

Please see attached image of above table

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Coder
  • 1
  • Define "last record of every customer". Are you looking for the highest SrNo on the last date the customer did a transaction? Given that you can have two series of SrNo's for each customer on each date, it would be hard to pick just one record for each customer. You could get the highest SrNo for each type of transaction in a query pretty easily. – James Jul 02 '21 at 12:44
  • **"Are you looking for the highest SrNo on the last date the customer did a transaction?"** Yes, I am. Please advise. Order is like [latest] Date, [highest] TranType and highest [SrNo]. – Coder Jul 02 '21 at 13:13

2 Answers2

0

You need another condition to match last SrNo. Try below query. Here tDate is your date column. As date is a reserve keyword, I used tDate as column name.

select t.*
from Table1 as t
inner join (
    select CustID, max(tdate) as MaxDate, max(SrNo) as MaxSr
    from Table1
    group by CustID
) as t2 on t.CustID = t2.CustID and t.tdate = t2.MaxDate and t.SrNo=t2.MaxSr

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Dear Harun, Thank you for prompt reply, I did try what you have suggested but 'max(SrNo)' does not return the max(SrNo) relative to the max(TDate), instead it searches the entire database of the current 'CustID' and returns max(SrNo). e.g. if 'D' has done five transactions on 25-Feb-2021, then this query will return Max(Date) as 02-Jan-2021 and Max(SrNo) as 5 – Coder Jul 02 '21 at 09:38
  • @Coder I assumed `SrNo` is based on each date. Means everyday `SrNo` starts from 1 and increase as much as records inserted. If that is not the case then how you will determine which record is last transaction for a customer for each day? Any other logic? – Harun24hr Jul 02 '21 at 09:47
0

SQL tables represent unordered sets. So you need a second column to get the last one per day. This would be solved if the date column had a time component, but that does not seem to be the case. Let me assume that SrNo provides the ordering within a day.

If so:

select t.*
from t
where t.date = (select max(t2.date) 
                from t as t2 
                where t2.custId = t.custId
               ) and
      t.SrNo = (select max(t2.SrNo) 
                from t as t2 
                where t2.custId = t.custId and t2.date = t.date
               ) ;

The first condition gets values on the last date. The second gets the last value on the date, assuming that SrNo provides that ordering.

For performance, you want an index on (custId, date, srNo).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, SrNo does provide an ordering. Tried the query as you suggested but it did not return any result, waited for more than 15 minutes; still it is showing 'Running query' message in Status Bar below. Please note, I have many rows in the Access database. – Coder Jul 02 '21 at 12:03
  • @Coder . . . Try adding the recommended index. – Gordon Linoff Jul 02 '21 at 12:35