By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

Cumulative sums in queries

P: 1
I have a table with 2 columns, [Planned_Close_Date] and [Actual_Closed_Date].I want to run a query that will give me a cumulative sum of the no. of entries that have an [Actual_Closed_Date]<=[Planned_Closed_Date] and on the second column give me a cumulative sum of the no. of entries for [Planned_Closed_Date]. These cumulative sums will be grouped by {Planned_Close_Date]

Here is what the CODE I have so far, it does not give me cumulated sums.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDataStore.PlannedCloseDate, Count(tblDataStore.PlannedCloseDate) AS CountOfPlannedCloseDate, Count(tblDataStore.ActualCloseDate) AS CountOfActualCloseDate
  2. FROM tblDataStore
  3. GROUP BY tblDataStore.PlannedCloseDate
  4. HAVING (((tblDataStore.PlannedCloseDate) Not Like ""));
  5.  
Jun 29 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
I have a table with 2 columns, [Planned_Close_Date] and [Actual_Closed_Date].I want to run a query that will give me a cumulative sum of the no. of entries that have an [Actual_Closed_Date]<=[Planned_Closed_Date] and on the second column give me a cumulative sum of the no. of entries for [Planned_Closed_Date]. These cumulative sums will be grouped by {Planned_Close_Date]

Here is what the CODE I have so far, it does not give me cumulated sums.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDataStore.PlannedCloseDate, Count(tblDataStore.PlannedCloseDate) AS CountOfPlannedCloseDate, Count(tblDataStore.ActualCloseDate) AS CountOfActualCloseDate
  2. FROM tblDataStore
  3. GROUP BY tblDataStore.PlannedCloseDate
  4. HAVING (((tblDataStore.PlannedCloseDate) Not Like ""));
  5.  
Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDataStore.PlannedCloseDate, Count(tblDataStore.PlannedCloseDate) AS CountOfPlannedClose, Count(tblDataStore.ActualCloseDate) AS CountOfActualClose, DSum(“[CountOfPlannedClose]”,”tblDataStore”) As CumulativePlanned, DSum(“[CountOfActualClose]”,”tblDataStore”) As CumulativeActual, DSum(“[CountOfActualClose]”,”tblDataStore”,” [Actual_Close_Date]<=[Planned_Close_Date]”) As ClosedAheadOfSchedule
  2. FROM tblDataStore
  3. GROUP BY tblDataStore.PlannedCloseDate
  4. HAVING (((tblDataStore.PlannedCloseDate) Not Like ""));
  5.  
Jul 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.