Hello, I am trying to create a SQL query or VBA code in Access 2003 which will allow me to find multiple records for an order, sum the interval between the start and end dates of those records. Where overlapping dates exist, it would sum the total days from the earliest day to the latest day without duplication of days, such as:

Order Start End

1 5/1/06 5/6/06
1 5/4/06 5/10/06
1 5/3/06 5/11/06
2 5/1/06 5/5/06
2 5/8/06 5/10/06

Resulting With

Order Total Days
1 (11)
2 (7)

Each "Order" could have any number of records. They could overlap or not. If I do a straight sum, clearly Order #1 would come back with duplication of days, this is the scenario I am trying to avoid. I do not know If I just haven't had enough coffee yet, but I cannot seem to get my head around this!

I know I cannot be the first person who has come up against this problem, and was wondering if someone could point me in the right direction. My ultimate need is to subtract the final number from an Overall interval, so using either a query or code to even create a table which I could then link for the remaining calculations would work just fine for me.

Any assistance would be greatly appreciated!

Thanks