Crosstab query help please

G

Guest

Good morning:

I am working in Access 2000, and I am trying 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 the
cross tab query or the underlying query to take these totals and calculate
them as a running sum from month to month (i.e. add up Aug 2005 transactions,
then carry over into Sept 2005, and calculate the new Sept 2005 total, then
carry over Sept 2005 into October 2005, etc). This is what I would like the
cross tab query to return:

example: there were 100 totes in stock in Aug 2005; 100 more were
processed and also in stock at the end of Sept 2005; in October, all
transactions for that variety showed a total of 40 being shipped out for the
month. The overall total for October would equal 160 in stock. This is how
I want the cross:

Variety Unit Ped Total In Stock Aug 2005 Sept 2005
Oct 2005...etc
Soybean 1000 kg #1 0 100 200
160

This is what my cross tab is currently returning:

Variety Unit Ped Total In Stock Aug 2005 Sept 2005
Oct 2005...etc
Soybean 1000 kg #1 0 100 100
-40

I am attaching the SQL for the cross tab query, as well as the underlying
select query that it is based on for your review.

Cross tab query SQL:

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

Monthly Inventory #2 SQL (Select query based on Monthly Inventory query):

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

Monthly Inventory SQL (select query based on customer order details table):

SELECT DISTINCTROW Format$([customer order details].[date ordered],'mmmm
yyyy') AS [date ordered By Month], [customer order details].Productname,
[customer order details].Type, [customer order details].unit, [customer order
details].[Pedigree/Status], [customer order details].Warehouse, Sum([customer
order details].[Carry Over Balance]) AS [Sum Of Carry Over Balance],
Sum([customer order details].[Released Product]) AS [Sum Of Released
Product], Sum([customer order details].[Quantity Rec'd]) AS [Sum Of Quantity
Rec'd], Sum([customer order details].QuantityDel) AS [Sum Of QuantityDel],
Sum([customer order details].[Qty Plot Seed]) AS [Sum Of Qty Plot Seed],
Sum([customer order details].[Qty Ret'd by Cust]) AS [Sum Of Qty Ret'd by
Cust], Sum([customer order details].[Qty Transfer Out]) AS [Sum Of Qty
Transfer Out], Sum([customer order details].[Qty Ret'd by Shareholder]) AS
[Sum Of Qty Ret'd by Shareholder], Sum([customer order details].[Qty
Adjusted]) AS [Sum Of Qty Adjusted], Sum([customer order details].[Qty
Dumped]) AS [Sum Of Qty Dumped]
FROM [customer order details]
GROUP BY Format$([customer order details].[date ordered],'mmmm yyyy'),
[customer order details].Productname, [customer order details].Type,
[customer order details].unit, [customer order details].[Pedigree/Status],
[customer order details].Warehouse, Year([customer order details].[date
ordered])*12+DatePart('m',[customer order details].[date ordered])-1
HAVING ((([customer order details].Type)="Soybeans") AND (([customer order
details].Warehouse) Like "Released*"));

I am not acquainted with working in the SQL view of the queries, so I am
unable to determine a solution to this problem. If anybody has any
suggestions I would appreciate any help.
 
D

David F Cox

I do not have time to wade through your SQL. I would generate a query that
did the accumlative sum. One way is to use something like (UK dates):

aug: IIF( [yourdate]< 1/8/2006,[QTYx]-[QTYy], 0)
sep:IIF( [yourdate]< 1/9/2006,[QTYx]-[QTYy], 0)
etc
and sum these fields.



K. L. Collins said:
Good morning:

I am working in Access 2000, and I am trying 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 the
cross tab query or the underlying query to take these totals and calculate
them as a running sum from month to month (i.e. add up Aug 2005
transactions,
then carry over into Sept 2005, and calculate the new Sept 2005 total,
then
carry over Sept 2005 into October 2005, etc). This is what I would like
the
cross tab query to return:

example: there were 100 totes in stock in Aug 2005; 100 more were
processed and also in stock at the end of Sept 2005; in October, all
transactions for that variety showed a total of 40 being shipped out for
the
month. The overall total for October would equal 160 in stock. This is
how
I want the cross:

Variety Unit Ped Total In Stock Aug 2005 Sept 2005
Oct 2005...etc
Soybean 1000 kg #1 0 100 200
160

This is what my cross tab is currently returning:

Variety Unit Ped Total In Stock Aug 2005 Sept 2005
Oct 2005...etc
Soybean 1000 kg #1 0 100 100
-40

I am attaching the SQL for the cross tab query, as well as the underlying
select query that it is based on for your review.

Cross tab query SQL:

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

Monthly Inventory #2 SQL (Select query based on Monthly Inventory query):

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

Monthly Inventory SQL (select query based on customer order details
table):

SELECT DISTINCTROW Format$([customer order details].[date ordered],'mmmm
yyyy') AS [date ordered By Month], [customer order details].Productname,
[customer order details].Type, [customer order details].unit, [customer
order
details].[Pedigree/Status], [customer order details].Warehouse,
Sum([customer
order details].[Carry Over Balance]) AS [Sum Of Carry Over Balance],
Sum([customer order details].[Released Product]) AS [Sum Of Released
Product], Sum([customer order details].[Quantity Rec'd]) AS [Sum Of
Quantity
Rec'd], Sum([customer order details].QuantityDel) AS [Sum Of QuantityDel],
Sum([customer order details].[Qty Plot Seed]) AS [Sum Of Qty Plot Seed],
Sum([customer order details].[Qty Ret'd by Cust]) AS [Sum Of Qty Ret'd by
Cust], Sum([customer order details].[Qty Transfer Out]) AS [Sum Of Qty
Transfer Out], Sum([customer order details].[Qty Ret'd by Shareholder]) AS
[Sum Of Qty Ret'd by Shareholder], Sum([customer order details].[Qty
Adjusted]) AS [Sum Of Qty Adjusted], Sum([customer order details].[Qty
Dumped]) AS [Sum Of Qty Dumped]
FROM [customer order details]
GROUP BY Format$([customer order details].[date ordered],'mmmm yyyy'),
[customer order details].Productname, [customer order details].Type,
[customer order details].unit, [customer order details].[Pedigree/Status],
[customer order details].Warehouse, Year([customer order details].[date
ordered])*12+DatePart('m',[customer order details].[date ordered])-1
HAVING ((([customer order details].Type)="Soybeans") AND (([customer order
details].Warehouse) Like "Released*"));

I am not acquainted with working in the SQL view of the queries, so I am
unable to determine a solution to this problem. If anybody has any
suggestions I would appreciate any help.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can a cross tab query calculate a running sum? 3
Adding up row values 4
Can a crosstab query perform a running total? 3
Can somebody help me with this cross tab query? 1
Combining 3 queries 2
Back Order Fill 3
two queries from same data in table into one query 2
Update Field On Differant Form 1

Top