Date Range causing ill effects?

  • Thread starter Thread starter Gary De Velder
  • Start date Start date
G

Gary De Velder

Dear Access Experts,

I am a total novice with Access 20002. Thanks for any and all helpful
advice.

I have two related tables (one to many) used to track contributions, one
for donor data and the other for donors' contribution data. I want to
see donors in a particular date range so I can ultimately merge one
letter per donor showing the total of his/her contributions during that
period. In the query design I have no problem when I place a one month
date range in GIFTDATE and Sum in GIFTAMOUNT. Works perfectly. That is,
I get one record per each person who contributed that month with a total
$ amount for any number of contributions. However, when I expand the
date range more than one month it seems like nothing else works. The Sum
in GIFTAMOUNT no longer has any effect. All the individual donations are
listed, so a donor's record is shown any number of times rather than
just once.

In summary, when I don't use the GIFTDATE field at all Sum works fine in
GIFTAMOUNT and donors are listed once with a total of their
contributions. Same is true when there is a one month range in GIFTDATE.
But,Sum does not work when GIFTDATE is expanded to more than one month.

I'm obviously doing something wrong, but what?

Thanks,
Gary
 
Jeff said:
Gary

Please post the SQL statement you are using for the query.
Click to expand...

Thanks Jeff,

Further analysis while I was getting these SQL's indicates that Sum
totals only those gifts which have the same GIFTDATE. The date range
apparently has nothing to do with it. The SQL is:

SELECT [Newmas~1].LNAME, [Newmas~1].FNAME, Gifts.GIFTDATE,
Sum(Gifts.GIFTAMOUNT) AS SumOfGIFTAMOUNT
FROM [Newmas~1] INNER JOIN Gifts ON [Newmas~1].[ID RECORD] = Gifts.[Donor#]
GROUP BY [Newmas~1].LNAME, [Newmas~1].FNAME, Gifts.GIFTDATE
HAVING (((Gifts.GIFTDATE) Between #10/1/2003# And #10/31/2003#))
ORDER BY [Newmas~1].LNAME, [Newmas~1].FNAME;

When I expand the date range in GIFTDATE it still totals the
contributions in GIFTAMOUNT that were dated the same day. The SQL is:

SELECT [Newmas~1].FNAME, [Newmas~1].LNAME, Gifts.GIFTDATE,
Sum(Gifts.GIFTAMOUNT) AS SumOfGIFTAMOUNT
FROM [Newmas~1] INNER JOIN Gifts ON [Newmas~1].[ID RECORD] = Gifts.[Donor#]
GROUP BY [Newmas~1].FNAME, [Newmas~1].LNAME, Gifts.GIFTDATE
HAVING (((Gifts.GIFTDATE) Between #1/1/2003# And #12/31/2003#));

So I guess my question really is this: How do I end up with one total
per donor within any designated date range?

Thanks again.
Gary
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT N.FNAME, N.LNAME, Min(G.GiftDate) As FirstGift,
Max(G.GiftDate) As LastGift, Sum(G.GIFTAMOUNT) AS Total
FROM [Newmas~1] As N INNER JOIN Gifts As G
ON N.[ID RECORD] = G.[Donor#]
WHERE G.GIFTDATE Between #1/1/2003# And #12/31/2003#
GROUP BY N.FNAME, N.LNAME



- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDkZ8YechKqOuFEgEQLTzwCg79h0L2nVhns6nQq7tmKk4o+f/dIAoNc1
Qn93NwtL/JEufKVTN+ZfvWUU
=hlAC
-----END PGP SIGNATURE-----


Gary De Velder wrote:
 
Amazing! It worked perfectly! Now all I have to do if figure out the
reasoning behind what you did so I can do it again. I appreciate the
help and quick response.

Gary
 
Back
Top