Sum between Dates

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

I am working on a database to track the electrical bill each month. My
current problem is how to total the payments made between the old statement
and the new one. I have two tables, tblStatements and tblPayments both of
which have autonumbering ID fields and a date field, (StateDate and PayDate)
but they do not have a field that can be used to create a relationship. Using
a sub-query I have a qryStatement with the current statement date and the
previous statement date on the same row. Additionally, the statements are not
a calendar month, they run from the second week of one month to the second
week of the next month. I should also mention that there could be multiple
payments made during one statement. So how can I get the statement dates and
the total payments made before that date and greater than the previous date
on the same row? One solution I considered is to capture the StatementID when
a payment is made but then what's the point of having dates. Anyway, I am
looking for a way to use the dates to calculate the total payments during a
statement period. As always, any help would be appreciated.
 
Hi Michael,

Try this:

In the access query panel create a new query. Add your qryStatement and the
tblPayments
to the query.

Add the fields which hold the currentstatementdate and the previousstatement
date
sourced from the qryStatement. Then add the Paydate and PaymentAmount from
the tblPayments.

Set a criteria on the PayDate of "> previousstatementdate and <=
currentstatementdate.

I believe the payments and statements will be aligned.

They used to refer to this as setting a relation in the "WHERE" clause.

Regards

Kevin
 
Kevin,
Thanks, that worked. It's basically a cartesian product with criteria which
got me the results, so thanks.
--
Michael Conroy
Stamford, CT


kc-mass said:
Hi Michael,

Try this:

In the access query panel create a new query. Add your qryStatement and the
tblPayments
to the query.

Add the fields which hold the currentstatementdate and the previousstatement
date
sourced from the qryStatement. Then add the Paydate and PaymentAmount from
the tblPayments.

Set a criteria on the PayDate of "> previousstatementdate and <=
currentstatementdate.

I believe the payments and statements will be aligned.

They used to refer to this as setting a relation in the "WHERE" clause.

Regards

Kevin
Click to expand...
 
Back
Top