Group and sum past due columns in a Crosstab query

[puup]

I have Access Crosstab query that Sum's units per week(Monday Dates) by Planner ID. Displays 18 to 20 weeks of data. It looks like:
Planner ID 7/7/03 8/4/03 9/2/03 9/8/03(current wk)
C 8
D 1
E 4
0 5

SQL VIEW IS:
TRANSFORM Sum([SumOfQty])
SELECT [Planner ID]
FROM [WEEKS SUMMED]
GROUP BY [Planner ID]
PIVOT Format([MondayOfWeek],"Short Date"

Is there any way to take the past due columns (weeks less than MondayOfCurrent week)
Make one column AS PastDue and Sum the totals
Like this:
Planner ID PastDue 9/8/03 9/15/03
C 8
D 1
E 4
0 5

 

[dhookom]

The following will create a total of PastDues.
TRANSFORM Sum([SumOfQty])
SELECT [Planner ID], Abs(Sum([SumOfQty] * [MondayOfWeek]<Date())) As PastDue
FROM [WEEKS SUMMED]
GROUP BY [Planner ID]
PIVOT Format([MondayOfWeek],&quot;Short Date&quot;

This will not remove them from creating their own column. I always create relative date column headings. For instance a column heading would be:
ColHead: &quot;W&quot; & DateDiff(&quot;w&quot;,[MondayOfWeek],DateAdd(&quot;d&quot;,-WeekDay(Date())+2,Date()))
Then set the column headings property to:
&quot;W0&quot;,&quot;W1&quot;,&quot;W2&quot;,W#&quot;,...




Duane
MS Access MVP

 

[puup]

I Love the PastDue column....but since this will not keep those ugly past due date columns from creating their own identity box, I guess the Access crosstab is not the way to go. It must be simple for Manufacturing supervisors.
They just want to see qty PastDue and qty per week for 18-20 weeks. They are also partial to those Monday dates in the column headings also.
Is it possible to change the SQL code in an Access SELECT query to get the results I'm looking for?

SELECT query code is:

SELECT WEEKS.[Planner ID], Sum(WEEKS.Qty) AS SumOfQty, WEEKS.MondayOfWeek
FROM WEEKS
GROUP BY WEEKS.[Planner ID], WEEKS.MondayOfWeek
Thanks-A-Bunch!

 

[dhookom]

I would use the method I suggested with hard-coding the Column Headings property. This will keep your derived columns within a specific range. The results should be printed in a report which works very well with the fixed column headings.

You can create column labels in your report with text boxes:
=DateAdd(&quot;w&quot;,0,DateAdd(&quot;d&quot;,-WeekDay(Date())+2,Date()))
=DateAdd(&quot;w&quot;,-1,DateAdd(&quot;d&quot;,-WeekDay(Date())+2,Date()))
...
The above is not tested but you should be able to create expressions based on the current date for the columns in the report.

Duane
MS Access MVP