K
K. L. Collins
Hi:
I would like some aditional help with this crosstab query that I've been
trying to get working. Michael Walsh responded to my earlier posting, but so
far, I'm not having any luck. This is the query SQL that I have based the
crosstab query on:
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, Sum([customer
order details].[Carry Over Balance]) AS [Sum Of Carry Over Balance],
Sum([customer order details].[Quantity Rec'd]) AS [Sum Of Quantity Rec'd],
Sum([customer order details].[Released Product]) AS [Sum Of Released
Product], 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 Dumped]) AS [Sum Of Qty
Dumped], Sum([customer order details].[Qty Adjusted]) AS [Sum Of Qty
Adjusted], Sum([Carry Over Balance]+[Released Product]+[Quantity
Rec'd]-[QuantityDel]-[Qty Plot Seed]-[Qty Ret'd by Cust]-[Qty Transfer
OUt]-[Qty Dumped]-[Qty Adjusted]) AS [Total In Stock]
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, Year([customer order details].[date
ordered])*12+DatePart('m',[customer order details].[date ordered])-1
HAVING ((([customer order details].Type)<>"Packaging"));
What I would like the crosstab query to calculate is the overall inventory
at month end for each product and unit size on the floor, but also to
calculate as a running total (ie take the Total In Stock for Aug 2004,
add/subtract all entries for Sept 2004 and return the final month end total,
then add/subtract Oct 2004 entries, return the final month end total, etc.)
For example: If there were 500 bags at the end of August, and September saw
0 movement, and in October 300 bags were received and 400 shipped out, this
is what I want the query to return:
VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004......
Venus Soys 25 kg 500 500 400
When I create the crosstab query, this is what it is actually returning:
VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004....
Venus Soys 25 kg 500 Null -100
Mr. Walsh has told me that I need to make the running sum before making the
crosstab, but with the information that he gave to me, I was unable to make
it work. I have never worked before in the SQL view before, and I do not
understand it. If somebody out there could please help me find a way to make
my above-mentioned query perform the sum, I would greatly appreciate it.
Thank you!
I would like some aditional help with this crosstab query that I've been
trying to get working. Michael Walsh responded to my earlier posting, but so
far, I'm not having any luck. This is the query SQL that I have based the
crosstab query on:
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, Sum([customer
order details].[Carry Over Balance]) AS [Sum Of Carry Over Balance],
Sum([customer order details].[Quantity Rec'd]) AS [Sum Of Quantity Rec'd],
Sum([customer order details].[Released Product]) AS [Sum Of Released
Product], 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 Dumped]) AS [Sum Of Qty
Dumped], Sum([customer order details].[Qty Adjusted]) AS [Sum Of Qty
Adjusted], Sum([Carry Over Balance]+[Released Product]+[Quantity
Rec'd]-[QuantityDel]-[Qty Plot Seed]-[Qty Ret'd by Cust]-[Qty Transfer
OUt]-[Qty Dumped]-[Qty Adjusted]) AS [Total In Stock]
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, Year([customer order details].[date
ordered])*12+DatePart('m',[customer order details].[date ordered])-1
HAVING ((([customer order details].Type)<>"Packaging"));
What I would like the crosstab query to calculate is the overall inventory
at month end for each product and unit size on the floor, but also to
calculate as a running total (ie take the Total In Stock for Aug 2004,
add/subtract all entries for Sept 2004 and return the final month end total,
then add/subtract Oct 2004 entries, return the final month end total, etc.)
For example: If there were 500 bags at the end of August, and September saw
0 movement, and in October 300 bags were received and 400 shipped out, this
is what I want the query to return:
VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004......
Venus Soys 25 kg 500 500 400
When I create the crosstab query, this is what it is actually returning:
VARIETY TYPE UNIT AUG 2004 SEPT 2004 OCT 2004....
Venus Soys 25 kg 500 Null -100
Mr. Walsh has told me that I need to make the running sum before making the
crosstab, but with the information that he gave to me, I was unable to make
it work. I have never worked before in the SQL view before, and I do not
understand it. If somebody out there could please help me find a way to make
my above-mentioned query perform the sum, I would greatly appreciate it.
Thank you!