Query running sum - help again

  • Thread starter Thread starter jtfalk
  • Start date Start date
J

jtfalk

Sorry for having to ask again but due to my lack of providing the correct
information what was answered did not help. Here is what I have so far in my
SQL of my report that has a chart on it.

SELECT DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]) AS WeekOf,
Count(Issues.ID) AS [Total Tags], Sum(IIf([Validation Date]<>0,1,0)) AS [Tags
Closed], Sum(IIf([Validation Date]<>0,0,1)) AS [Tags Opened] FROM Issues
WHERE (((Issues.Department) Like
[Forms]![ViewSpecificReportsDSSChart]![Combo14] & "*") AND ((Issues.[Machine
Station]) Like [Forms]![ViewSpecificReportsDSSChart]![Combo24] & "*") AND
((IIf([Validation Date]<>0,"Closed","Open")) Like
[Forms]![ViewSpecificReportsDSSChart]![Combo16] & "*")) GROUP BY
DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]);


WeekOf Total Tags Tags Closed Tags Opened
7/26/2009 9 6 3
8/2/2009 14 7 7
8/30/2009 3 1 2
9/6/2009 10 8 2
9/13/2009 8 4 4
9/20/2009 10 3 7
9/27/2009 2 0 2
10/4/2009 8 1 7
10/11/2009 1 0 1

So I want to have a running sum of Total tags, From above though you can see
that this number changes based on the department selected. So I need it to
also follow the [Forms]![ViewSpecificReportsDSSChart]![Combo14] & "*" type of
look from the department lookup. This is from the form that gets the
department number. The other 2 combo's are for the station nad status (if
wanted). if all boxes are left empty then it does them all.
 
Keep your actual query as it is, don't touch it, except to 'save' it (say it
is saved under the name q1).

Make a NEW query, bring your saved query twice, one reference will
automatically get the name q1_1.

Make this new query a total query.

From the reference q1, bring weekOf in the grid, keep the proposed GROUP
BY.
Do it again, but this time, under this second column, change the GROUP BY to
WHERE and add the criteria: >= [q1_1].[weekOf]
Bring TagsOpened from q1_1 in the grid, change the GROUP BY to SUM.


That's all.


Vanderghast, Access MVP
 
I just have to say - thank you so much.

It works perfect

vanderghast said:
Keep your actual query as it is, don't touch it, except to 'save' it (say it
is saved under the name q1).

Make a NEW query, bring your saved query twice, one reference will
automatically get the name q1_1.

Make this new query a total query.

From the reference q1, bring weekOf in the grid, keep the proposed GROUP
BY.
Do it again, but this time, under this second column, change the GROUP BY to
WHERE and add the criteria: >= [q1_1].[weekOf]
Bring TagsOpened from q1_1 in the grid, change the GROUP BY to SUM.


That's all.


Vanderghast, Access MVP


jtfalk said:
Sorry for having to ask again but due to my lack of providing the correct
information what was answered did not help. Here is what I have so far in
my
SQL of my report that has a chart on it.

SELECT DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]) AS WeekOf,
Count(Issues.ID) AS [Total Tags], Sum(IIf([Validation Date]<>0,1,0)) AS
[Tags
Closed], Sum(IIf([Validation Date]<>0,0,1)) AS [Tags Opened] FROM Issues
WHERE (((Issues.Department) Like
[Forms]![ViewSpecificReportsDSSChart]![Combo14] & "*") AND
((Issues.[Machine
Station]) Like [Forms]![ViewSpecificReportsDSSChart]![Combo24] & "*") AND
((IIf([Validation Date]<>0,"Closed","Open")) Like
[Forms]![ViewSpecificReportsDSSChart]![Combo16] & "*")) GROUP BY
DateAdd("D",-Weekday([Issue Date])+1,[Issue Date]);


WeekOf Total Tags Tags Closed Tags Opened
7/26/2009 9 6 3
8/2/2009 14 7 7
8/30/2009 3 1 2
9/6/2009 10 8 2
9/13/2009 8 4 4
9/20/2009 10 3 7
9/27/2009 2 0 2
10/4/2009 8 1 7
10/11/2009 1 0 1

So I want to have a running sum of Total tags, From above though you can
see
that this number changes based on the department selected. So I need it to
also follow the [Forms]![ViewSpecificReportsDSSChart]![Combo14] & "*" type
of
look from the department lookup. This is from the form that gets the
department number. The other 2 combo's are for the station nad status (if
wanted). if all boxes are left empty then it does them all.
Click to expand...
Click to expand...
 
Back
Top