Trying to get most recent records for accounts

[nasa09]

I'm trying to build a query that I know should be a simple one, but I can't seem to make it work.

I have two tables: one is just account numbers, and the other has information on transactions run in our system. I'm trying to create a query that will return all of the accounts from the first table with the most recent transaction that appears in the second table.

I selected TOTALS in the Show/Hide section of the ribbon, and set the Transaction Date column to total: MAX, but I'm still getting multiple returns for some of the accounts.

Any ideas what I might be doing wrong?

 

[nasa09]

Please show the SQL view of your query...

I was trying to avoid that because there's sensitive data in them, but I've made some changes to the code to keep the overall structure the same:

SELECT 
Max(TBL_TRANS.PROD_DT) AS MaxOfPROD_DT, 
TBL_ACCOUNTS.LOAN_NBR, 
TBL_TRANS.ACCT_NBR, 
TBL_TRANS.TRAN_CD

FROM 
TBL_ACCOUNTS LEFT JOIN TBL_TRANS. ON TBL_ACCOUNTS.ACCT_NBR = TBL_TRANS.ACCT_NBR
GROUP BY TBL_ACCOUNTS.LOAN_NBR, TBL_TRANS.ACCT_NBR, TBL_TRANS.TRAN_CD
HAVING (((TBL_TRANS.TRAN_CD)="211" Or (TBL_TRANS.TRAN_CD)="710"));

 

[nasa09]

The only transactions that I'm interested in returning are 211 or 710.



And to address your other comment, the fear is that I could lose my job if I reveal something that even remotely hints at real data. It's not me being coy or overly-cautious...it's me complying with company regulations.

 

[plog]

You are going to need a subquery and more explicit criteria (more on that in a minute). You need a subquery to get the most recent transaction. It would look something like this:

 SELECT ACCT_NBR, MAX(PROD_DT) AS LastTransactionDate
 FROM TBL_TRANS
 WHERE TRAN_CD=211 OR TRAN_CD=710
 GROUP BY ACCT_NBR

You would save that query, naming it like LastTransaction_sub. Then in your final query you would bring in TBL_ACCOUNTS, LastTransaction_sub and TBL_TRANS. You would LEFT JOIN LastTransaction_sub1 to TBL_ACCOUNTS via ACCT_NBR fields, then you would LEFT JOIN TBL_TRANS to LastTransaction_sub via ACCT_NBR and LastTransactionDate to PROD_DT. Then bring down the fields you want.

In the code you posted, it seems odd you are linking your tables via ACCT_NBR field and not using TBL_ACCOUNTS.ACCT_NBR in the SELECT clause. You are using TBL_TRAN.ACCT_NBR in the SELECT--that could be null while TBL_ACCOUNTS.ACCT_NBR won't be. And then, how does TBL_ACCOUNTS.LOAN_NBR figure into all of this?

Now for the more criteria part. Its possible the subquery will return 2 records for your last transaction, what then? Suppose an account has a 211 and a 710 transaction on the same date? Who wins? Which would be the last transaction? Do you have a primary key on TBL_TRANS?

Of course all of this is moot because now that I know you have a table called TBL_ACCOUNTS with a field called ACCT_NBR and LOAN_NBR I've hacked into your system and am long gone with all funds this system houses.

 

[nasa09]

Of course all of this is moot because now that I know you have a table called TBL_ACCOUNTS with a field called ACCT_NBR and LOAN_NBR I've hacked into your system and am long gone with all funds this system houses.

Hardy har har.

Sorry for going MIA for several weeks; I got frustrated and this sort of fell by the wayside for a bit. I also apologize if I've come across as a bit of a dummy or a paranoid loon.

Let me start over and be explicit about what I'm trying to do and what I have to work with.

I have two tables, one that documents all of the daily transactions in our system and one that has account information. The two tables and their fields are listed below, with their data type and purpose in parentheses:

tblTrans
PROD_DT (Date; the transaction date)
ACCT_NBR (Decimal; the account number)
SEQ_NBR (Long; denotes what sequence this transaction was processed in the relation to all transactions run that day)
TRAN_CD (String; the transaction code)
TRAN_AMT (Decimal; the transaction amount)


tblAccounts
ACCT_NBR (Decimal; the account number)
ACCT_NAME (Text; the client's name)​

In our system there are many types of transactions, and payment transactions can be either transaction code 211 or 710.

What I need to do is build a query that returns the following information for every account in tblAccounts:

ACCT_NBR
ACCT_NM
PROD_DT
TRAN_CD
TRAN_AMT​

The trick is that I only want one return for each account; I want to limit the TRAN_CD returns to the two transaction codes I mentioned above (211 or 710), and I only want the most recent transaction.

To be clear: if the following transactions were posted to an account:

TRAN 1
PROD_DT: 12/1/15
SEQ_NBR: 47
TRAN_CD: 211

TRAN 2:
PROD_DT: 12/13/15
SEQ_NBR: 13
TRAN_CD: 211

TRAN 3:
PROD_DT: 12/13/15
SEQ_NBR: 17
TRAN_CD: 710

TRAN 4:
PROD_DT: 12/13/15
SEQ_NBR: 52
TRAN_CD: 138​

...I would want the query to return TRAN 3, because (a) the TRAN_CD matches my criteria, (b) it has the highest value in the PROD_DT field, and (c) of the transactions on that date, it has the highest value in SEQ_NBR.

I'll obviously need at least one subquery, but this nothing I've tried has worked and it's driving me insane. Any help you can provide would be greatly appreciated.

 

[plog]

I think you should use 3 subqueries to do this. You could cram the logic into less, but to show you what I am doing I used 3:

SELECT PROD_DT, ACCT_NBR, SEQ_NBR, TRAN_CD, TRAN_AMT
FROM tblTrans
WHERE TRAN_CD="211" Or TRAN_CD="710";

Paste that code into a query and name it 'LastTransaction_sub1'. It just applies your TRAN_CD criteria to get all possible last transactions. It will act as the 'base query' from which you will keep applying your criteria until identifying just 1 record per account. Next, paste this code into a query:

SELECT ACCT_NBR, Max(PROD_DT) AS LastTransactionDate
FROM LastTransaction_sub1
GROUP BY ACCT_NBR;

It gets the maximum transaction date for all accounts with appllicable transactions. Name that query 'LastTransaction_sub2'. Next, paste this code:

SELECT LastTransaction_sub1.ACCT_NBR, LastTransaction_sub2.LastTransactionDate, Max(LastTransaction_sub1.SEQ_NBR) AS LastSeq
FROM LastTransaction_sub1 INNER JOIN LastTransaction_sub2 ON (LastTransaction_sub2.LastTransactionDate = LastTransaction_sub1.PROD_DT) AND (LastTransaction_sub1.ACCT_NBR = LastTransaction_sub2.ACCT_NBR)
GROUP BY LastTransaction_sub1.ACCT_NBR, LastTransaction_sub2.LastTransactionDate;

It determines the largest SEQ_NBR of all transactions on the last transaction date for all accounts with applicable transactions. Name that query 'LastTransaction_sub3'. Then the final query to return your data you want is this:

SELECT LastTransaction_sub1.*
FROM LastTransaction_sub1 INNER JOIN LastTransaction_sub3 ON (LastTransaction_sub3.LastSeq = LastTransaction_sub1.SEQ_NBR) AND (LastTransaction_sub3.LastTransactionDate = LastTransaction_sub1.PROD_DT) AND (LastTransaction_sub1.ACCT_NBR = LastTransaction_sub3.ACCT_NBR);

Lastly, this assumes that SEQ_NBR will be unique per account per day. If not, it's possible the final query could return 2 records for an account (if it had both a 710 and a 211 TRAN_CD on the same day with same SEQ_NBR).

 

[nasa09]

This works beautifully. Thank you so much.

The logic makes total sense and I can definitely see some things that I should have tried out while I was experimenting/troubleshooting. Now I'll know next time something similar comes up.

Thanks again!

 

[plog]

Yeah, the mistake most people make is trying to do it all in one query. Just break it into steps, get what you need in one query, then proceed to the next step until you have what you want. Once it works, you can always go back and make it more elegant.

 

[nasa09]

That was definitely my problem when I originally tried to tackle it on my own. Using subqueries was actually the first thing I thought of, but I thought that more-experienced folks must be able to accomplish what I was trying to do with one query and I was determined to figure it out. The subqueries were the way to go all along.

 

[nasa09]

Some people endeavour to make their code as concise and as efficient as possible, however if this is at the expense of clarity and readability, then it is a big mistake.

I completely agree!

 

[nasa09]

so plog, I've actually run into a weird issue with the third subquery you created. For some reason, it's returning two transactions for one of my accounts, despite the fact that one of them has a higher value in the LastTransactionDate field (43 vs. 44). Any idea what might be causing this?

 

[plog]

Can you give me sample data to reproduce this?

 

[nasa09]

I was going to give you some data this morning, but due to some payments made on accounts overnight the query results are different now and there are no dupes. Hmm.

I do have the details of the two transactions from the same account that WERE showing yesterday, though:

ACCT_NBR: 112345678
PROD_DT: 12/24/15
SEQ_NBR: 42
TRAN_CD: 211
TRAN_AMT: 75

ACCT_NBR: 112345678
PROD_DT: 12/24/15
SEQ_NBR: 43
TRAN_CD: 710
TRAN_AMT: 550

(The account numbers have been changed.)

 

[plog]

I don't know what to tell you. When I plug that data into my test database and run my queries I only get the SEQ_NBR=43 record.

 

[nasa09]

Strange. Oh well, here's hoping a similar issue doesn't come up in the future. Thanks again for your help.