Building Report, Filter Individual Textbox By Date

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?
LVL 16
anoyesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
re> how to get a sum of membership payments where the year is 2007,2006,2005, etc.

SELECT C.ContactID, C.FirstName, C.LastName, C.Address, C.City, C.StateOrProvince, M.MbrshpAmt, M.MbshpDte, right((Year(Now())-3),2) & "_" & right((Year(Now())-2),2) & "_" & right((Year(Now())-1),2) As Years
FROM Contacts C INNER JOIN [Membership Info] M ON C.ContactID= M.ContactID
WHERE Year([MbshpDte])=Year(Now())-1 Or  Year([MbshpDte])=Year(Now()-2 Or Year([MbshpDte])=Year(Now()-3;

I added a new fields to your SQL with alias name of Years. For now being 2008, its values would be 05_06_07. In your report great a gooup header and group footer for this field [Years] and the in the footer ad a txtDum and enter =Sum([MbrshpAmt]) in its controls source.

Mike

b) modify this SQL so I can get back a value for each year group?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
...correction

I added a new fields to your SQL with alias name of Years. For now being 2008, its values would be 05_06_07. In your report create a group header and group footer for this field named [Years] and then in the footer section add a text box txtSum and enter =Sum([MbrshpAmt]) in its controls source.
0
Mike EghtebasDatabase and Application DeveloperCommented:
You can have only group footer by setting group head visible property to false.

if you name the caption lable of this text box (txtDum) as lblSum

in the same footer section add:

Me!lblSum.Caption ="Sum for yesrs: " & Me!txtYears

fo the lable of txtSum to read:

Sum for yesrs: 05_06_07

But you need to add txtYears bound to [Years] to your footer section with visible property set to false.

Mike
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

anoyesAuthor Commented:
Correct me if I'm wrong, but it looks like what this SQL is doing is providing me a sum of 2005+2006+2007, which I've already got.  What is need is to get 2005 AND 2006 AND 2007, all as separate values.  The idea is the report would look something like this:

Name | Address | City | 2007 | 2006 | 2005 | Total

where the value for each year is the sum of the payments made in that year.  So for example 2007 would be Sum(MbshpAmt) but ONLY where the MbshpDte's year value is 2007, same for 2006, 2005, etc.
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:. it looks like what this SQL is doing is providing me a sum of 2005+2006+2007

No. SQL is providing a dummy [Years] field (you may call it).

For Now() at                [Years]
----------------            -------------
2007                          04_05_06
2008                          05_06_07
2009                          06_07_08

dianamically. So you can add a groupping to your report to have the sum value to be shown.

If you are alrady groupping this report by Name then have Years' groupping inside Name goupping.

I hope this helps.

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
correction...

So you can add a groupping to your report to have the sum value to be shown in the footer section of Years group.
0
anoyesAuthor Commented:
Thanks for your help.  I was able to get the info I needed using a different approach that didn't involve grouping the report further.  For any interested, here's the SQL:

SELECT Contacts.ContactID, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.City, Contacts.StateOrProvince, [Membership Info].MbrshpAmt, [Membership Info].MbshpDte, (SELECT SUM(MbrshpAmt) FROM [Membership Info] WHERE [Membership Info].ContactID=Contacts.ContactID AND YEAR(MbshpDte)=YEAR(NOW())-1) AS Year1, (SELECT SUM(MbrshpAmt) FROM [Membership Info] WHERE [Membership Info].ContactID=Contacts.ContactID AND YEAR(MbshpDte)=YEAR(NOW())-2) AS Year2, (SELECT SUM(MbrshpAmt) FROM [Membership Info] WHERE [Membership Info].ContactID=Contacts.ContactID AND YEAR(MbshpDte)=YEAR(NOW())-3) AS Year3
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));
0
Mike EghtebasDatabase and Application DeveloperCommented:
Fine with me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.