Two most recent transactions for each customer

Giganews Newsgroups
Subject: Two most recent transactions for each customer
Posted by:  rjroxspo…@gmail.com
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###
                SELECT t1.policyNumber
        FROM aig2_Transaction AS t1
        WHERE
            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

Replies