Query count day over day 1

[end922]

Greetings, I am not sure how to even pose this question. I have a query that does a group by on a date then sums each category.
Each day they have. carried over from previous day, New, Complete, Cancelled, Pending for next day.
like so
Date.....carry .... new .... comp ... can .... pending
03/16..... 5 ....... 10 ..........7........0..........8
03/17..... 8 ........20 ..........15.......0..........21
and so on.
Pending would be a sum from each day that was left over from that day and the previous days. Then carry would be = to the previous days pending.
16th 8 was left over and the 17th 13 was left over so my total for the 17th is 21.
I am doing this in an Access query. Any suggestions where I could start.
Apprecaite your help.

Eric

 

[MichaelRed]

No help until the explination and math math match, at least not from me.

3/16 looks like (5 + 10) - 8 = 15
3/17 looks like (8 + 20) - 15 = 13 (NOT 21?)

So what is correct?



MichaelRed

 

[end922]

Gotcha bad example. Can I send you an example of what I am trying to do? May I e-mail it to you?

 

[end922]

Current Query Results
Date Carry New Comp Canned Pending
16-Mar-09 0 19 17 0 2
17-Mar-09 0 47 45 0 2
18-Mar-09 0 19 17 0 2
19-Mar-09 0 25 18 1 6
20-Mar-09 0 33 23 0 10

Currently Pending column will only show the number of items pending for that day. Carry column is null.

Desired Query Results
Date Carry New Comp Canned Pending
16-Mar-09 0 19 17 0 2
17-Mar-09 2 47 45 0 4
18-Mar-09 4 19 17 0 6
19-Mar-09 6 25 18 1 12
20-Mar-09 12 33 23 0 22

Desired Pending column will sum pending from the previous day + pending from current day. Afterwards Carry will = the pending from previous day.

 

[PHV]

What is the SQL code of your actual query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

I knew you were going to ask that question.

query1

SELECT Master.ID, Master.General, Master.DateReqRec, Master.DateReqEntered, IIf([DateReqRec]=[DateReqEntered],[DateReqRec],[DateReqEntered]) AS 1, IIf([ADPIssued] Is Null,0,1) AS [Comp], IIf([ADPIssued] Is Null,1,0) AS Pending, Master.StandardizedComments, Master.Metric1, Master.Metric2, Master.Metric3, Master.Metric4, IIf([DateReqEntered] Is Null,1,0) AS B1, IIf([DateReqEntered] Is Not Null And [DateApproved] Is Null,1,0) AS B2, IIf([DateApproved] Is Not Null And [DateNAOP] Is Null,1,0) AS B3, IIf([DateNAOP] Is Not Null And [ADPIssued] Is Null,1,0) AS B4 INTO TR1
FROM Master
WHERE (((Master.General)=True) AND ((Master.DateReqEntered) Between [Start Date] And [End Date]));

query2

SELECT TR1.[1], TR1.Comp, IIf([StandardizedComments]=2,1,0) AS Can, TR1.Pending, IIf([metric1] Is Null,0,[metric1]) AS M1, IIf([metric2] Is Null,0,[metric2]) AS M2, IIf([metric3] Is Null,0,[metric3]) AS M3, IIf([metric4] Is Null,0,[metric4]) AS M4, TR1.B1, TR1.B2, TR1.B3, TR1.B4 INTO TR2
FROM TR1;

query 3

SELECT TR2.[1], Count(TR2.[1]) AS CountOf1, Sum(TR2.Comp) AS SumOfComp, Sum(TR2.Can) AS SumOfCan, Sum(TR2.Pending) AS SumOfPending, Avg(TR2.M1) AS AvgOfM1, Avg(TR2.M2) AS AvgOfM2, Avg(TR2.M3) AS AvgOfM3, Avg(TR2.M4) AS AvgOfM4, Sum(TR2.B1) AS SumOfB1, Sum(TR2.B2) AS SumOfB2, Sum(TR2.B3) AS SumOfB3, Sum(TR2.B4) AS SumOfB4 INTO TR3
FROM TR2
GROUP BY TR2.[1];

query 4

SELECT TR3.[1] AS [Date], "" AS Carry, TR3.CountOf1 AS New, TR3.SumOfComp AS [Comp], TR3.SumOfCan AS Canned, ([New]-[Comp])-[cANNED] AS Pending, Round([AvgOfM1],2) AS M1, Round([AvgOfM2],2) AS M2, Round([AvgOfM3],2) AS M3, Round([AvgOfM4],2) AS M4, Round([m1]+[m2]+[m3]+[m4],2) AS M5, TR3.SumOfB1 AS B1, TR3.SumOfB2 AS B2, TR3.SumOfB3 AS B3, TR3.SumOfB4 AS B4 INTO DataFile
FROM TR3;

Forget the metric# and the B# those work fine.

 

[PHV]

A starting point:

SELECT [Date], Nz(DSum("Pending","DataFile","[Date]<#" & Format([Date],"yyyy-mm-dd")  & "#"),0) AS myCarry
, New, Comp, Canned, myCarry+New-Comp-Canned AS myPending
FROM DataFile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[end922]

Thank you sir. It is a good starting point.
Appreciate your assistance.
E