|Subject:||Two most recent transactions for each customer|
|Date:||Sat, 28 Apr 2018|
Hey there, totally struggling with this I searched for a response already out there and didn't see one so i apologize if I missed it.
MS Access 2010, I have a transactions table for insurance policies which have a effective and expiration date. I need to select the last transactions for every customer if they have a transaction in the last year plus the policy immediately before.
Put another way, last two transactions for every policy with the most recent based on a date. Too many things to list all things I've tried but is what i think is the closest.
The query below works to get all transactions for each property, but I only want the top 2 of EACH policy. Is this even possible?
####SELECT ALL TRANSACTIONS FOR ALL POLICIES THAT FIT######
SELECT t2.policyNumber, t2.TransactionID, t2.PolicyEffectiveDate, t2.PolicyExpirationDate
FROM aig2_Transaction AS t2
WHERE t2.policyNumber IN
####Go GET ALL POLICIES THAT FIT WITHIN THE Date range###
FROM aig2_Transaction AS t1
t1.TransactionCodeID IN (1,2)
AND t1.PolicyEffectiveDate >= 01/01/2017
AND t1.PolicyEffectiveDate <= 01/01/2018
ORDER BY t1.policyNumber
ORDER BY t2.policyNumber, t2.TransactionID