Can a crosstab query perform a running total?

[Guest]

In Access 2000, I have created a cross tab query based on a select query. The
goal of the query is to total all movement of all the varieties each month,
and return the overall current quantity in stock as of month end. What I
would like the query to do is to take the month end inventory from the
starting month in the sequence (in our case, August 2004), and either add or
subtract September's transactions to this quantity, and return the overall
quantity in stock as of the end of September, and so on. For example, if at
the end of August, there were 500 bags of a particular variety in stock, and
September saw no transactions of that variety, September's month end total
should also show 500 in inventory. If October's transactions saw a total of
300 more received in, and a total of 400 bags of that same variety shipped
out, then October's month end total should show that there are now 400 bags
in stock. Is it possible for the cross tab (or the select) query to
calculate this kind of a running total, or is there alternate solution to
this?

The cross tab SQL information is as follows;

TRANSFORM Sum([Monthly Inventory].[Total In Stock]) AS [SumOfTotal In Stock]
SELECT [Monthly Inventory].Productname,[Monthly Inventory].Type,[Monthly
Inventory].unit, Sum([Monthly Inventory].[Total In Stock])AS [Total of Total
In Stock]
FROM [Monthly Inventory]
GROUP BY [Monthly Inventory].Productname, [Monthly Inventory].Type, [Monthly
Inventory].unit
PIVOT [Monthly Inventory].[date ordered By Month] In ("Aug 2004","Sept
2004","Oct 2004","Nov 2004","Dec 2004","Jan 2005","Feb 2005","March
2005","May 2005","Apr 2005","June 2005","July 2005");

This is what I would like the query to generate (based on the same
quantities mentioned in the first paragraph):

VARIETY TYPE UNIT AUG 2004 SEPT 2004 0CT 2004....

Venus Soys 25 kg 500 500 400

This is what the cross tab query is currently returning:

VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004...

Venus Soys 25 kg 500 Blank -100


Any help at all on this would be appreciated (the less technical, the
better!)

Thank you!

 

[Michel Walsh]

Hi,

You have to make the running sum BEFORE making the crosstab.


SELECT a.ProductName,
a.DateTimeOfTransaction,
SUM(b.in - b.out)
FROM inventory As a INNER JONI inventory As b
ON a.ProductName=b.ProductName AND a.DateTimeOfTransaction >=
b.DateTimeOfTransaction
GROUP BY a.ProductName, a.DateTimeOfTransaction



The third column of this query is the running sum. Build a crosstab using
that saved query as starting point (rather than using the table, directly).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi,

Thank you for your response; however, I am not familiar with working in the
SQL view, so I haven't been able to figure out exactly how to utilize this
information. I am working with a database set up by another individual, and
this is a query that I have created, and am not having much sucess with.

If it helps, this is the SQL information from the query that the crosstab
query is based upon:

SELECT [Monthly Inventory #1].[date ordered by Month], [Monthly Inventory
#1].Productname, [Monthly Inventory#1].Type, [Monthly Inventory #1].unit,
[Monthly Inventory #1].[Sum Of Carry Over Balance], [Monthly Inventory
#1].[Sum Of Released Product], [Monthly Inventory #1].[Sum of Quantity
Rec'd], [Monthly Inventory #1].[Sum Of QuantityDel], [Monthly Inventory
#1].[Sum Of Qty Plot Seed], [Monthly Inventory #1].[Sum Of Qty Ret'd by
Cust], [Monthly Inventory #1].[Sum Of Qty Transfer Out], [Monthly Inventory
#1].[Sum Of Qty Dumped], [Sum Of Carry Over Balance]+[Sum Of Released
Product]+[Sum Of Quantity Rec'd]-[Sum Of Quantity Del]-[Sum Of Qty Plot
Seed]-[Sum Of Qty Ret'd by Cust]-[Sum Of Qty Transfer Out]-[Sum Of Qty
Dumped]-[Sum Of Qty Adjusted] AS [Total In Stock]
FROM [Monthly Inventory #1];

So, what I would like to know is if the information that you gave to me
somehow needs to be inserted into the above SQL statement, or if it is
necessary for me to create another select query in order to accomplish the
running sum, and then create the crosstab query. I hate to sound dumb, but I
will admit that I'm stumped by this. I have never worked within the SQL view
before, so all of this technical stuff is beyond me.

Thanks again for any help!

Hi,

You have to make the running sum BEFORE making the crosstab.


SELECT a.ProductName,
a.DateTimeOfTransaction,
SUM(b.in - b.out)
FROM inventory As a INNER JONI inventory As b
ON a.ProductName=b.ProductName AND a.DateTimeOfTransaction >=
b.DateTimeOfTransaction
GROUP BY a.ProductName, a.DateTimeOfTransaction



The third column of this query is the running sum. Build a crosstab using
that saved query as starting point (rather than using the table, directly).



Hoping it may help,
Vanderghast, Access MVP

In Access 2000, I have created a cross tab query based on a select query.
The
goal of the query is to total all movement of all the varieties each
month,
and return the overall current quantity in stock as of month end. What I
would like the query to do is to take the month end inventory from the
starting month in the sequence (in our case, August 2004), and either add
or
subtract September's transactions to this quantity, and return the overall
quantity in stock as of the end of September, and so on. For example, if
at
the end of August, there were 500 bags of a particular variety in stock,
and
September saw no transactions of that variety, September's month end total
should also show 500 in inventory. If October's transactions saw a total
of
300 more received in, and a total of 400 bags of that same variety shipped
out, then October's month end total should show that there are now 400
bags
in stock. Is it possible for the cross tab (or the select) query to
calculate this kind of a running total, or is there alternate solution to
this?

The cross tab SQL information is as follows;

TRANSFORM Sum([Monthly Inventory].[Total In Stock]) AS [SumOfTotal In
Stock]
SELECT [Monthly Inventory].Productname,[Monthly Inventory].Type,[Monthly
Inventory].unit, Sum([Monthly Inventory].[Total In Stock])AS [Total of
Total
In Stock]
FROM [Monthly Inventory]
GROUP BY [Monthly Inventory].Productname, [Monthly Inventory].Type,
[Monthly
Inventory].unit
PIVOT [Monthly Inventory].[date ordered By Month] In ("Aug 2004","Sept
2004","Oct 2004","Nov 2004","Dec 2004","Jan 2005","Feb 2005","March
2005","May 2005","Apr 2005","June 2005","July 2005");

This is what I would like the query to generate (based on the same
quantities mentioned in the first paragraph):

VARIETY TYPE UNIT AUG 2004 SEPT 2004 0CT 2004....

Venus Soys 25 kg 500 500 400

This is what the cross tab query is currently returning:

VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004...

Venus Soys 25 kg 500 Blank -100


Any help at all on this would be appreciated (the less technical, the
better!)

Thank you!

 

[Michel Walsh]

Hi,


A SQL statement, or a query, aggregate (SUM) data vertically, not
horizontally, so that is why you need to make the SUM before you make the
crosstab so than the running sum occur across the records, vertically, not
across the fields, horizontally.


Hoping it may help,
Vanderghast, Access MVP

Hi,

Thank you for your response; however, I am not familiar with working in
the
SQL view, so I haven't been able to figure out exactly how to utilize this
information. I am working with a database set up by another individual,
and
this is a query that I have created, and am not having much sucess with.

If it helps, this is the SQL information from the query that the crosstab
query is based upon:

SELECT [Monthly Inventory #1].[date ordered by Month], [Monthly Inventory
#1].Productname, [Monthly Inventory#1].Type, [Monthly Inventory #1].unit,
[Monthly Inventory #1].[Sum Of Carry Over Balance], [Monthly Inventory
#1].[Sum Of Released Product], [Monthly Inventory #1].[Sum of Quantity
Rec'd], [Monthly Inventory #1].[Sum Of QuantityDel], [Monthly Inventory
#1].[Sum Of Qty Plot Seed], [Monthly Inventory #1].[Sum Of Qty Ret'd by
Cust], [Monthly Inventory #1].[Sum Of Qty Transfer Out], [Monthly
Inventory
#1].[Sum Of Qty Dumped], [Sum Of Carry Over Balance]+[Sum Of Released
Product]+[Sum Of Quantity Rec'd]-[Sum Of Quantity Del]-[Sum Of Qty Plot
Seed]-[Sum Of Qty Ret'd by Cust]-[Sum Of Qty Transfer Out]-[Sum Of Qty
Dumped]-[Sum Of Qty Adjusted] AS [Total In Stock]
FROM [Monthly Inventory #1];

So, what I would like to know is if the information that you gave to me
somehow needs to be inserted into the above SQL statement, or if it is
necessary for me to create another select query in order to accomplish the
running sum, and then create the crosstab query. I hate to sound dumb,
but I
will admit that I'm stumped by this. I have never worked within the SQL
view
before, so all of this technical stuff is beyond me.

Thanks again for any help!

Hi,

You have to make the running sum BEFORE making the crosstab.


SELECT a.ProductName,
a.DateTimeOfTransaction,
SUM(b.in - b.out)
FROM inventory As a INNER JONI inventory As b
ON a.ProductName=b.ProductName AND a.DateTimeOfTransaction >=
b.DateTimeOfTransaction
GROUP BY a.ProductName, a.DateTimeOfTransaction



The third column of this query is the running sum. Build a crosstab using
that saved query as starting point (rather than using the table,
directly).



Hoping it may help,
Vanderghast, Access MVP



message

In Access 2000, I have created a cross tab query based on a select
query.
The
goal of the query is to total all movement of all the varieties each
month,
and return the overall current quantity in stock as of month end. What
I
would like the query to do is to take the month end inventory from the
starting month in the sequence (in our case, August 2004), and either
add
or
subtract September's transactions to this quantity, and return the
overall
quantity in stock as of the end of September, and so on. For example,
if
at
the end of August, there were 500 bags of a particular variety in
stock,
and
September saw no transactions of that variety, September's month end
total
should also show 500 in inventory. If October's transactions saw a
total
of
300 more received in, and a total of 400 bags of that same variety
shipped
out, then October's month end total should show that there are now 400
bags
in stock. Is it possible for the cross tab (or the select) query to
calculate this kind of a running total, or is there alternate solution
to
this?

The cross tab SQL information is as follows;

TRANSFORM Sum([Monthly Inventory].[Total In Stock]) AS [SumOfTotal In
Stock]
SELECT [Monthly Inventory].Productname,[Monthly
Inventory].Type,[Monthly
Inventory].unit, Sum([Monthly Inventory].[Total In Stock])AS [Total of
Total
In Stock]
FROM [Monthly Inventory]
GROUP BY [Monthly Inventory].Productname, [Monthly Inventory].Type,
[Monthly
Inventory].unit
PIVOT [Monthly Inventory].[date ordered By Month] In ("Aug 2004","Sept
2004","Oct 2004","Nov 2004","Dec 2004","Jan 2005","Feb 2005","March
2005","May 2005","Apr 2005","June 2005","July 2005");

This is what I would like the query to generate (based on the same
quantities mentioned in the first paragraph):

VARIETY TYPE UNIT AUG 2004 SEPT 2004 0CT 2004....

Venus Soys 25 kg 500 500 400

This is what the cross tab query is currently returning:

VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004...

Venus Soys 25 kg 500 Blank -100


Any help at all on this would be appreciated (the less technical, the
better!)

Thank you!