CODEWITHSUNDEEP
vbams-access
David Frick
David Frick

Reputation: 776

Access Comparing Two Records from Today and Yesterday

I have a table called Past_Wires that looks like this (partly)

Eff_Date | Portfolio | Balance
6/13/2017  0JR         10000
6/13/2017  S25         10000
6/14/2017  0JR         0
6/14/2017  S25         20000

I want to run a query that compares the balances of each portfolio to the previous date's balance (hopefully yesterday's date) and return the difference. I am pretty sure this needs to be done in VBA (which I can do) and cannot be done in a query itself, but wanted to see if anyone had ideas.

Upvotes: 0

Views: 67

Answers (1)

serakfalcon
serakfalcon

Reputation: 3531

JOIN the table to itself.

Something like

SELECT a.Balance as oldBalance, b.Balance as newBalance, a.Eff_Date as OldDate, b.Eff_Date as NewDate
FROM Past_Wires as a
INNER JOIN Past_Wires as b ON a.Portfolio = b.Portfolio AND a.Eff_Date<b.Eff_Date

You could also use DateDiff('d',a.Eff_Date,b.Eff_Date) = 1 in the WHERE clause to only compare subsequent days with each other.

Upvotes: 1

Related Questions