Skip to Content
0
Former Member
Aug 21, 2014 at 02:23 PM

How To 'Split' Records

23 Views

Hello everyone,

I have an Access db table with the following fields:

DelayID, StartTime, FinishTime

with a sample data like this:

1 7/1/2014 8:00 7/1/2014 10:00

2 7/1/2014 22:00 7/2/2014 3:00

3 7/2/2014 15:00 7/5/2014 18:00

I would like to create the following line chart in CR 11.5 R2:

X axis - date in day increments such as 7/1/2014, 7/2/2014, ...

Y axis - duration in hours from 0 to 24.

The chart would need to show cumulative delay duration for each day like this:

7/1/2014 4 hours ( 2 hours of DelayID 1 + 2 hours of DelayID 2)

7/2/2014 12 hours ( 3 hours of DelayID 2 + 9 hours of DelayID 3)

7/3/2014 24 hours ( DelayID 3)

7/4/2014 24 hours ( DelayID 3)

7/5/2014 18 hours ( DelayID 3)

The line graph only shows 2 points (7/1 and 7/2) while I need 5 points (7/1 through 7/5).

I tried using many different queries with no luck.

Is there a way to somehow split the 3 records into 5 so that the chart is created correctly using SQL?

Thanks.