K
K. L. Collins
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!
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!