Group by month - bleed to subsequent months

[zulumika]

Hi all, I'm new here. Great forum btw!



Here's a tough one (at least, for me).

I'm a volunteer at a palliative care center. I'm building a DB to gather statistics on patients occupation.

I want to build a report with a DateDiff that's going to bleed or pour the extra days onto the next month.

An example:
On Jan 1st, Bob is admitted in the center. On March 10th, he dies.
So if we calculate the number of days he stayed, we get 31+28+10=69
Bob stayed at the center for 69 days.

I calculated the DateDiff on the report, grouped by month (the month of the admission) and it's all fine. So I get Bob under Jan, he stayed 69 days. Yeah, cool. But there's only 31 days in Jan. Not so cool.

What I need is the span (I don't even know how to call this), I need the occupancy for each month. This number cannot exceed the number of days in x month. If the patient stays for more than a month, or his stay covers (or crosses) multiple months, I need this to show in the report.

The same example:
Bob stayed 31 days in Jan, so under Jan, we get 31 and not 69 (the initial DateDiff).
Bob stayed 28 days in feb (not a leap year), we get 28.
Bob stayed 10 days in march, so 10.

Do I have to program an algorithm to loop calculate how many days in x month and subtract this from the total occupation (if the number of days figures in multiple months)? Is there another way, something easier? I'm starting to loose sleep because of this...

At this point, anything will help. Thanks all!

[June7]

Access can't report records that don't exist.

I am seeing in your future a VBA procedure looping a recordset and saving records to a temp table and report is based on temp table.

Temp table = table is permanent, records are temporary, table records purged after process completes.

Otherwise, let users do the math. I can look at a value of 69 days and know that this covers several months.

[zulumika]

Thanks for your suggestions. However I can't let the user "do the math" since a patient can stay for a short period, let's say 12 days, and still figure in 2 different months. They are already doing everything by hand; I'm trying to automate this process...

[June7]

Then your options:

1. structure database and business procedures to enter the data manually

2. develop VBA code to write records to table - this has been a topic of numerous threads: given a start date and end date, create daily records.

As you can see, question has been asked
http://bytes.com/topic/access/answer...date-range-vba
http://www.tek-tips.com/viewthread.cfm?qid=1714016