B
brfender
Hi,
I have query that feeds a report in Access 2003. The query is based off of
2 tables (tblMain and tblDates).
Basically, there is one record in tblMain that could have one or multiple
dates stored in tblDates. The record in tblDates includes a start date and
stop date. I would like to count the number of calendar days between the
start and stop date. That is the easy part.
What I can't determine is trying to count days on overlapping records. For
example:
RecordA from tblMain:
DateRecord1 from tblDates: StartDate: 06/01/2006 and StopDate: 06/15/2006
DateRecord2 from tblDates: StartDate: 06/05/2006 and StopDate: 06/10/2006
DateRecord3 from tblDates: StartDate: 06/20/2006 and StopDate: 06/30/2006
The result should not calculate DateRecord2 because it is within
DateRecord1. It should only calculate the number of days in DateRecord1 and
the number of days in DateRecord3. The query sums the 2 together based on
the record in tblMain.
I'm sure I'm not explaining myself very well, but if anyone can read minds,
I certainly appreciate it.
Thank you in advance.
I have query that feeds a report in Access 2003. The query is based off of
2 tables (tblMain and tblDates).
Basically, there is one record in tblMain that could have one or multiple
dates stored in tblDates. The record in tblDates includes a start date and
stop date. I would like to count the number of calendar days between the
start and stop date. That is the easy part.
What I can't determine is trying to count days on overlapping records. For
example:
RecordA from tblMain:
DateRecord1 from tblDates: StartDate: 06/01/2006 and StopDate: 06/15/2006
DateRecord2 from tblDates: StartDate: 06/05/2006 and StopDate: 06/10/2006
DateRecord3 from tblDates: StartDate: 06/20/2006 and StopDate: 06/30/2006
The result should not calculate DateRecord2 because it is within
DateRecord1. It should only calculate the number of days in DateRecord1 and
the number of days in DateRecord3. The query sums the 2 together based on
the record in tblMain.
I'm sure I'm not explaining myself very well, but if anyone can read minds,
I certainly appreciate it.
Thank you in advance.