Running Sum from Crosstab

[cabbey77]

Hi,

My initial crosstab is as thus:
TRANSFORM Sum([ct AllCallsAdjSurgDt].Quantity) AS SumOfQuantity
SELECT [ct AllCallsAdjSurgDt].Hub_Name, [ct AllCallsAdjSurgDt].Kit_No, Sum([ct AllCallsAdjSurgDt].Quantity) AS [Total Of Quantity]
FROM [ct AllCallsAdjSurgDt]
WHERE ((([ct AllCallsAdjSurgDt].AdjustedSurgeryDate) Between #6/1/2014# And #8/1/2014#))
GROUP BY [ct AllCallsAdjSurgDt].Hub_Name, [ct AllCallsAdjSurgDt].Kit_No
PIVOT [ct AllCallsAdjSurgDt].AdjustedSurgeryDate;


Now I need a second query, whereby I have the exact same column headings as the above CT (2 months worth of data currently but will grow) but for each date I need the sum of the current column plus the next 3 columns. The Dates have been adjusted to eliminate weekends.

The absolute final result is a union of these two crosstabs.

Clear as mud?

Thanks for your help!

C

 

[dhookom]

I'm having trouble visualizing "current column plus the next 3 columns". It looks like the columns are dates so you would be aggregating across 4 dates.

Duane
Hook'D on Access
MS Access MVP

 

[cabbey77]

That is correct, I am aggregating across dates, but the dates are not sequential due to the removal of the weekends.

 

[dhookom]

This seems like a really odd request to sum across a group of 4 dates. Can we assume this could be Mon-Thu or Tue-Fri or Wed-Mon?

I think one solution would begin with a table of dates (minus weekends) with an autonumber field [DateID] to number the records chronologically from the earliest to the maximum latest date. This would allow you to do a cartesian type join to sum where the [DateID] is between [DateID] and [DateID] - 3.

Duane
Hook'D on Access
MS Access MVP

 

[cabbey77]

Hello,

First, thanks for the help, and I will give that a shot. To appease your curiosity, 4 business days is the accepted cycle time of a kit going out and coming back, therefore to best determine peak demand, you have to sum 4 days consecutively over time to come up with how many kits are actually out at any given time.

Cheers,

C