Select Query after Sort

[kdilag]

Hello,

I have a transaction data set with these fields see attached txt file


How would I write the query that returns the first TRAN_TYPE B record (based on TRAN_ID) for each CLIENT_ID thus rendering a 3 row selection. IOW first sorting by CLIENT_ID asc, TRAN_TYPE desc, TRAN_ID asc then selecting the first row for each client.

Can anyone describe how to set this query up in access?



Thanks soooo much if you can help me,

K

[jzwp11]

Just to be clear, you want your output to look like this?

CLIENT_ID|TRAN_ID|TRAN_TYPE
1|606|B
2|646|B
3|441|B

[kdilag]

Yes - that is what I would like the query to return.

[jzwp11]

Create a query that just selects the B records

query Name qryTheBs

SELECT tblClientTrans.Client_ID, tblClientTrans.TRAN_ID, tblClientTrans.TRAN_TYPE
FROM tblClientTrans
WHERE (((tblClientTrans.TRAN_TYPE)="B"))
ORDER BY tblClientTrans.Client_ID, tblClientTrans.TRAN_ID;


Then you will need a nested query as follows:

SELECT qryTheBs.Client_ID, qryTheBs.TRAN_ID, qryTheBs.TRAN_TYPE
FROM qryTheBs
WHERE qryTheBs.Tran_ID in (SELECT TOP 1 Tran_ID FROM qryTheBs as Q1 WHERE Q1.Client_ID=qryTheBs.Client_ID ORDER BY Q1.TRAN_ID ASC)
ORDER BY qryTheBs.Client_ID, qryTheBs.TRAN_ID;

[kdilag]

Thanks jzwp11!

[jzwp11]

You're welcome!