I'm using MS Access to build a report that basically gives a summary of payments for membership. The idea is that there is one row per member, with their name, address, membership payment last year, membership payment 2 years ago, membership payment 3 years ago, and total payments over those three years. The years have to be calculated dynamically (i.e. right now they're 2007, 2006, 2005, but next year they need to be 2008,2007,2006, etc.) I have the title for each year created dynamically on the report, and I also have the total over the past 3 years. What I can't figure out, however, is how to get a sum of membership payments where the year is 2007,2006,2005, etc. Does this make sense? Below is the SQL I'm using for the query that gets all the info:
SELECT Contacts.ContactID, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, [Membership Info].MbrshpAmt, [Membership Info].MbshpDte
FROM Contacts INNER JOIN [Membership Info] ON Contacts.ContactID=[Membership Info].ContactID
WHERE (((Year([MbshpDte]))=Year(Now())-1 Or (Year([MbshpDte]))=Year(Now())-2 Or (Year([MbshpDte]))=Year(Now())-3));
Anyone know how to either a) filter a sum using the expression builder on an individual column in the report, or b) modify this SQL so I can get back a value for each year group?