Can a cross tab query calculate a running sum?

[Guest]

I am working in Access 2000, and I posted a similar question a while back,
but was unable to make this work. I would like to set up a cross tab query
that will calculate the monthly total of our product in stock. The select
query that I have created takes information from several fields in my
Customer Order Details table, calculates the total number of units processed,
received, shipped, returned, etc. per month of each variety/unit/pedigree,
and comes up with an overall total for that month. I then need either this
query or the cross tab query to take these totals and calculate them as a
running sum from month to month (i.e. add up Aug 2004 transactions, then
carry over into Sept 2004, and calculate the new Sept 2004 total, then carry
over Sept 2004 into October 2004, etc). This is what I would like the cross
tab query to return:

Product Unit Pedigree Aug 2004 Sept 2004 Oct 2004 Nov 2004 etc.
Var 1 1000 kg Cert 0 100 75 125

Var 2 25 kg Cert 0 1000 1000 50

This is what is being returned when I create the cross tab query:

Product Unit Pedigree Aug 2004 Sept 2004 Oct 2004 Nov 2004 etc.

Var 1 1000 kg Cert 0 100 -25 50

Var 2 25 kg Cert 0 1000 0 -450

Is there a way to have the cross tab query perform the running sum across
from month to month? Here is the SQL from the cross tab query:

TRANSFORM Sum([Monthly Inventory #2].[In Stock]) AS [SumOfIn Stock]
SELECT [Monthly Inventory #2].Productname, [Monthly Inventory #2].unit,
[Monthly Inventory #2].[Pedigree/Status], Sum([Monthly Inventory #2].[In
Stock]) AS [Total Of In Stock]
FROM [Monthly Inventory #2]
GROUP BY [Monthly Inventory #2].Productname, [Monthly Inventory #2].unit,
[Monthly Inventory #2].[Pedigree/Status]
PIVOT [Monthly Inventory #2].[date ordered By Month] In ("August
2004","September 2004","October 2004","November 2004","December
2004","January 2005","February 2005","March 2005","April 2005","May
2005","June 2005","July 2005","August 2005");

I have never worked within the SQL view of queries before, and any help will
be greatly appreciated. Thank you for your time!

 

[[MVP] S.Clark]

Not a single instance of the query. But, once a month is over, does the
data change? i.e. When Oct 05 is done, do the numbers for it get
recalculated?

If not, then all you have to do is write the current months numbers, and
calculate the total.

If I'm totally of track, then my standard answer applies: Use VBA and a
couple of queries to write the data to a temp table.

 

[Tom Ellison]

Dear K. L.:

I recommend you create a new query based on the crosstab and adding the
running sum there.

Tom Ellison

 

[Guest]

Thank you both for your replies...I've been away and have not had the
opportunity to try these, but I will try both suggestions and see what
happens.

Dear K. L.:

I recommend you create a new query based on the crosstab and adding the
running sum there.

Tom Ellison

I am working in Access 2000, and I posted a similar question a while back,
but was unable to make this work. I would like to set up a cross tab
query
that will calculate the monthly total of our product in stock. The select
query that I have created takes information from several fields in my
Customer Order Details table, calculates the total number of units
processed,
received, shipped, returned, etc. per month of each variety/unit/pedigree,
and comes up with an overall total for that month. I then need either
this
query or the cross tab query to take these totals and calculate them as a
running sum from month to month (i.e. add up Aug 2004 transactions, then
carry over into Sept 2004, and calculate the new Sept 2004 total, then
carry
over Sept 2004 into October 2004, etc). This is what I would like the
cross
tab query to return:

Product Unit Pedigree Aug 2004 Sept 2004 Oct 2004 Nov 2004 etc.
Var 1 1000 kg Cert 0 100 75 125

Var 2 25 kg Cert 0 1000 1000 50

This is what is being returned when I create the cross tab query:

Product Unit Pedigree Aug 2004 Sept 2004 Oct 2004 Nov 2004 etc.

Var 1 1000 kg Cert 0 100 -25 50

Var 2 25 kg Cert 0 1000 0 -450

Is there a way to have the cross tab query perform the running sum across
from month to month? Here is the SQL from the cross tab query:

TRANSFORM Sum([Monthly Inventory #2].[In Stock]) AS [SumOfIn Stock]
SELECT [Monthly Inventory #2].Productname, [Monthly Inventory #2].unit,
[Monthly Inventory #2].[Pedigree/Status], Sum([Monthly Inventory #2].[In
Stock]) AS [Total Of In Stock]
FROM [Monthly Inventory #2]
GROUP BY [Monthly Inventory #2].Productname, [Monthly Inventory #2].unit,
[Monthly Inventory #2].[Pedigree/Status]
PIVOT [Monthly Inventory #2].[date ordered By Month] In ("August
2004","September 2004","October 2004","November 2004","December
2004","January 2005","February 2005","March 2005","April 2005","May
2005","June 2005","July 2005","August 2005");

I have never worked within the SQL view of queries before, and any help
will
be greatly appreciated. Thank you for your time!