Link to home
Start Free TrialLog in
Avatar of anoyes
anoyesFlag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...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.
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
Avatar of anoyes

ASKER

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.
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
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.
Avatar of anoyes

ASKER

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));
Fine with me.