Calculating multiple date ranges

[bt_user]

I have a form with 5 possible teams listed for each person. Each Team has an activation and deactivation date. I have used the DateDiff Function on each team's dates to calculate the number of days each person worked on each team.

However, I would like to calculate the total days worked on all teams. I have one field for this answer. But I cannot figure out how to get it to realize that when days overlap, do not count them more than once.

For example,
Team 1, activate date (4/1/2003), deactivate date (5/1/2003)...Total: 30
Team 2, activate date (4/15/2003), deactivate date (5/15/2003)...Total: 30
Team 2, activate date (5/16/2003), deactivate date (5/31/2003)...Total: 15

I cannot just add these days together for answer of 75 because as you can see some of the days (4/15/2003-4/30/2003) overlap on two teams. So the real answer should be 60. How can I get this to watch for possible overlap?

Any suggestions? I am desperate.

Thank you so much!!!!

 

[bt_user]

bump! Please help!

 

[jobb]

here's kind of a crude solution, but I think it might work..

create two new tables

to create the first table, just do a "fill" series in Excel, starting with the earliest date, so your data should look like this:
5/1/03
5/2/03
5/3/03
fill that series to the latest possible date, then copy your column of excel dates into your new Access date table, with just one field, short date format.

Create a second table ("table 2"), with another short date field and and a text field for "names". Make your primary key on this table both of these fields!

the next is the tricky part.. I don't know exactly how you might do this, but you need to set up some sort of macro that will step through the form, running 5 append queries (5 teams) for each name on the form along with the dates from your new dates table according to the criteria

(ie.. Date from DateTable
criteria: Between [forms].[yourform].[activate1] and [forms].[yourform].[deactivate1]
append to date2 in Table2,

calculated field: name: [Forms].[yourform].[name]
append to name2 in table 2)

. . If you set warnings to NO in your macro, you won't have to put up with any prompts. Only those dates that the person was active on any team will be appended, with no dupes. Then you can do a calculated query, group by name, and counting # of names or dates in your "table 2".

I hope that made sense! Let me know if you have any questions. There is probably a much easier way, I'm sure, but that's all I've got.