EVERY DATE BETWEEN 2 DATES

[deekras]

i have a list of members and their begin dates and some have end dates. they pay once a month - each one can be on any day of the month. i need to figure out how much money we billed on each day and how much we actually received. the received part, i more or less have. i want to get the billing part.

any suggestions on how to get a report to check for every date - to see if the member was active and billed.

 

[Alexandre]

I don t manage to figure out your concrete situation through what you say, but basing myself on the topic of your post, here is a way to make a consolidation query on a date field, and get results grouped per day:

Table: tblPayments
Fields:
- PaymentDate
- PaymentAmount
...

SELECT DISTINCTROW Format([PaymentDate],"yyyy-mm-dd") AS PaymentsPerDay,
Sum(PaymentAmount) AS PaymentsPerDay
FROM tblPayments
GROUP BY Format([PaymentDate],"yyyy-mm-dd");

But this is quite unusual to need to group on every days between two dates. Usually you would want to look at weekly, monthly or quarterly results whilst the actual information would be stored with one or more entries per day.


Alex

[This message has been edited by Alexandre (edited 09-04-2001).]