Query question - limit amounts included in sum by date

[Helen]

I am using the following query to sum the payments for each contributor.

SELECT DISTINCTROW Payment.ContributorID, Contributor.NamePart1,
Contributor.NamePart2, Contributor.Status, Sum(Payment.Amount) AS [Sum Of
Amount]
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
GROUP BY Payment.ContributorID, Contributor.NamePart1,
Contributor.NamePart2, Contributor.Status, Contributor.ContributorID
HAVING (((Contributor.Status)<>"Inactive"))

I would like to limit the query to include only payments with a PaymentDate
whose year is earlier than an given year (ideally data entered when the query
is run).

Can someone please tell me how to do this. I would very much appreciate any
help!

Helen

 

[Mark Andrews]

Use an iif() expression if you need multiple columns with restrive sums or
just use a where clause if you want to restrict the whole query (where
clause would go between FROM and GROUP BY)

Example: WHERE Payment.Date < #1/1/2008#

Example: Sum(iif(Payment.Date < #1/1/2008#,Payment.Amount,0))

To use a query parameter passed a form there are dozens of sites that show
this techinique. I recommend
allen browne's website as a good place to start browsing.
http://www.allenbrowne.com/tips.html

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[John W. Vinson]

I would like to limit the query to include only payments with a PaymentDate
whose year is earlier than an given year (ideally data entered when the query
is run).

First off, use a WHERE clause (applied *before* the summing) rather than
HAVING (which does all the totalling and then discards the non-matching
values; it's only appropriate when you are applying criteria to the result of
a totals calculation); then include a criterion on the date field. In
addition, don't group by (or even include) the Payment.ContributorID. Try

SELECT DISTINCTROW
Contributor.NamePart1, Contributor.NamePart2, Contributor.Status,
Sum(Payment.Amount) AS [Sum Of Amount]
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
GROUP BY Contributor.NamePart1, Contributor.NamePart2, Contributor.Status
WHERE Contributor.Status<>"Inactive" AND Payment.PaymentDate <
DateSerial([Enter last year to be considered:] + 1, 1, 1);

If the user enters 2007, the DateSerial function will return 1/1/2008, and the
criterion will select records before that date.

 

[Helen]

Mark,

Thank you for the quick response. I tried your second suggestion. I had to
use the date in the form #01/01/2009# in order to get results returned
(#1/1/2009# returned no data). However, when I ran the version that did
return data, the query incorrectly included a payment with a PaymentDate =
11/11/2009.

Any ideas?

Use an iif() expression if you need multiple columns with restrive sums or
just use a where clause if you want to restrict the whole query (where
clause would go between FROM and GROUP BY)

Example: WHERE Payment.Date < #1/1/2008#

Example: Sum(iif(Payment.Date < #1/1/2008#,Payment.Amount,0))

To use a query parameter passed a form there are dozens of sites that show
this techinique. I recommend
allen browne's website as a good place to start browsing.
http://www.allenbrowne.com/tips.html

HTH,
Mark
RPT Software
http://www.rptsoftware.com

I am using the following query to sum the payments for each contributor.

SELECT DISTINCTROW Payment.ContributorID, Contributor.NamePart1,
Contributor.NamePart2, Contributor.Status, Sum(Payment.Amount) AS [Sum Of
Amount]
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
GROUP BY Payment.ContributorID, Contributor.NamePart1,
Contributor.NamePart2, Contributor.Status, Contributor.ContributorID
HAVING (((Contributor.Status)<>"Inactive"))

I would like to limit the query to include only payments with a
PaymentDate
whose year is earlier than an given year (ideally data entered when the
query
is run).

Can someone please tell me how to do this. I would very much appreciate
any
help!

Helen

 

[Helen]

John,

Thank you for the quick response. I received a syntax error when I ran the
query as formated, but when I moved the WHERE clause to between the FROM and
GROUP BY clauses, it worked perfectly!

The query that worked:

SELECT DISTINCTROW Contributor.NamePart1, Contributor.NamePart2,
Contributor.Status, Sum(Payment.Amount) AS [Sum Of Amount]
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
WHERE Contributor.Status<>"Inactive" AND Payment.PaymentDate <
DateSerial([Enter last year to be considered:] + 1, 1, 1)
GROUP BY Contributor.NamePart1, Contributor.NamePart2, Contributor.Status;

Thank you a million times over!!

Helen

I would like to limit the query to include only payments with a PaymentDate
whose year is earlier than an given year (ideally data entered when the query
is run).

First off, use a WHERE clause (applied *before* the summing) rather than
HAVING (which does all the totalling and then discards the non-matching
values; it's only appropriate when you are applying criteria to the result of
a totals calculation); then include a criterion on the date field. In
addition, don't group by (or even include) the Payment.ContributorID. Try

SELECT DISTINCTROW
Contributor.NamePart1, Contributor.NamePart2, Contributor.Status,
Sum(Payment.Amount) AS [Sum Of Amount]
FROM Contributor INNER JOIN Payment ON Contributor.ContributorID =
Payment.ContributorID
GROUP BY Contributor.NamePart1, Contributor.NamePart2, Contributor.Status
WHERE Contributor.Status<>"Inactive" AND Payment.PaymentDate <
DateSerial([Enter last year to be considered:] + 1, 1, 1);

If the user enters 2007, the DateSerial function will return 1/1/2008, and the
criterion will select records before that date.

 

[John W. Vinson]

I moved the WHERE clause to between the FROM and
GROUP BY clauses, it worked perfectly!

oops! My mistake. Glad you were able to correct it quickly!