Calculate by months

[Chesne]

Can anyone point me in the right direction please

I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.

I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem

TIA

[Scott]

Re: Calculate by months

I think you need to create a table that updates when you use your table you
mention below, you need it to have each individual day and the rate listed
that way you could easily calculate out your monthly rates. You could have
these second table auto populate using code. I don't think there is anyway
to do it with your current set up, atleast not easily.

"Chesne" <ashfield@parad ise.net.nzwrote in message
news:1178447521 .623043.97710@u 30g2000hsc.goog legroups.com...

Can anyone point me in the right direction please
>
I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.
>
I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem
>
TIA
>

[Bob Quintal]

Re: Calculate by months

Chesne <ashfield@parad ise.net.nzwrote in
news:1178447521 .623043.97710@u 30g2000hsc.goog legroups.com:

Can anyone point me in the right direction please
>
I have a table with the following fields [BeginDate],
Enddate], [Rate] in dollars) and [NoOfNights] in an
Accommodation db.
>
I would like to get a total for each month, however some of
the start and end dates of the bookings begin in one month and
end in the month(s) following. How do I sort out the problem
>
TIA
>

You can calculate the number of days from the start date to the
end of the month. You can also do the first of the (next) month
to the endDate.

This has a problem if the stay starts 31st of march and runs to
1st of may.
..


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[CDMAPoster@FortuneJames.com]

Re: Calculate by months

On May 6, 5:32 am, Chesne <ashfi...@parad ise.net.nzwrote :

Can anyone point me in the right direction please
>
I have a table with the following fields [BeginDate], Enddate], [Rate]
in dollars) and [NoOfNights] in an Accommodation db.
>
I would like to get a total for each month, however some of the start
and end dates of the bookings begin in one month and end in the
month(s) following. How do I sort out the problem
>
TIA

I see that you've been working on this problem for over two years:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b8fcbcf1c83ddd11

I use the DateIntersectio n function to help me get started with the
logic for the SQL statement. Once the SQL statement is providing
correct answers it is often feasible to replace the DateIntersectio n
function with a subquery or a join thereby eliminating dependence on
VBA.

James A. Fortune
CDMAPoster@Fort uneJames.com