Can somebody help me with this cross tab query?


G

Guest

Hi:

I first posted this question back in January, and I am still struggling to
get this crosstab query to work, utilizing the instructions that were
provided to me. Here is my original question:

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?

Since then, I have tweaked my select queries, but the crosstab is still not
performing the running sum correctly. It is totaling up each month's
transactions properly, but it is still not performing the math correctly from
month to month. Here is the SQL information from all 3 queries that I am
currently working with:

SELECT QUERY:

SELECT [Practice Monthly Inventory].[date ordered by Month], [Practice
Monthly Inventory].Productname, [Practice Monthly Inventory].Type, [Practice
Monthly Inventory].unit, [Practice Monthly Inventory].[SumOfCarry Over
Balance], [Practice Monthly Inventory].[SumOfQuantity Rec'd], [Practice
Monthly Inventory].[SumOfReleased Product], [Practice Monthly
Inventory].SumOfQuantityDel, [Practice Monthly Inventory].[SumOfQty Plot
Seed], [Practice Monthly Inventory].[SumOfQty Ret'd by Cust], [Practice
Monthly Inventory].[SumOfQty Transfer Out], [Practice Monthly
Inventory].[SumOfQty Dumped], [Practice Monthly Inventory].[SumOfQty
Adjusted], [Practice Monthly Inventory].[Total In], [Practice Monthly
Inventory].[Total Out], [Total In]-[Total Out] AS [Total In Stock], [Practice
Monthly Inventory].Warehouse
FROM [Practice Monthly Inventory];

RUNNING SUM QUERY TO BASE CROSSTAB QUERY ON:

SELECT a.Productname, a.unit, a.[date ordered by Month], Sum(b.[Total
In]-b.[Total Out]) AS Expr1
FROM [Practice Monthly Inventory 2nd Draft] AS a INNER JOIN [Practice
Monthly Inventory 2nd Draft] AS b ON (a.Productname = b.Productname) AND
(a.[date ordered by Month] = b.[date ordered by Month]) AND
(a.[unit]=b.[unit])
GROUP BY a.Productname, a.unit, a.[date ordered by Month];

CROSSTAB QUERY:

TRANSFORM Sum([Practice Monthly Inventory Final Draft].Expr1) AS SumOfExpr1
SELECT [Practice Monthly Inventory Final Draft].Productname, [Practice
Monthly Inventory Final Draft].unit, Sum([Practice Monthly Inventory Final
Draft].Expr1) AS [Total Of Expr1]
FROM [Practice Monthly Inventory Final Draft]
GROUP BY [Practice Monthly Inventory Final Draft].Productname, [Practice
Monthly Inventory Final Draft].unit
PIVOT [Practice Monthly Inventory Final Draft].[date ordered by Month] In
("August2004","September2004","October2004","November2004","December2004","January2005","February2005","March2005","April2005","May2005","June2005","July2005");

I know that there will probably be a simple and obvious answer to this
question, but I'm afraid that I'm not the person to spot it. Since I've
never really used the SQL view before, I don't know where I've gone wrong.
Any help from anybody out there will be greatly appreciated.

Thank you!
 
Ad

Advertisements

C

Chris2

K. L. Collins said:
Hi:

I first posted this question back in January, and I am still struggling to
get this crosstab query to work, utilizing the instructions that were
provided to me. Here is my original question:

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?

Since then, I have tweaked my select queries, but the crosstab is still not
performing the running sum correctly. It is totaling up each month's
transactions properly, but it is still not performing the math correctly from
month to month. Here is the SQL information from all 3 queries that I am
currently working with:

SELECT QUERY:

SELECT [Practice Monthly Inventory].[date ordered by Month], [Practice
Monthly Inventory].Productname, [Practice Monthly Inventory].Type, [Practice
Monthly Inventory].unit, [Practice Monthly Inventory].[SumOfCarry Over
Balance], [Practice Monthly Inventory].[SumOfQuantity Rec'd], [Practice
Monthly Inventory].[SumOfReleased Product], [Practice Monthly
Inventory].SumOfQuantityDel, [Practice Monthly Inventory].[SumOfQty Plot
Seed], [Practice Monthly Inventory].[SumOfQty Ret'd by Cust], [Practice
Monthly Inventory].[SumOfQty Transfer Out], [Practice Monthly
Inventory].[SumOfQty Dumped], [Practice Monthly Inventory].[SumOfQty
Adjusted], [Practice Monthly Inventory].[Total In], [Practice Monthly
Inventory].[Total Out], [Total In]-[Total Out] AS [Total In Stock], [Practice
Monthly Inventory].Warehouse
FROM [Practice Monthly Inventory];

RUNNING SUM QUERY TO BASE CROSSTAB QUERY ON:

SELECT a.Productname, a.unit, a.[date ordered by Month], Sum(b.[Total
In]-b.[Total Out]) AS Expr1
FROM [Practice Monthly Inventory 2nd Draft] AS a INNER JOIN [Practice
Monthly Inventory 2nd Draft] AS b ON (a.Productname = b.Productname) AND
(a.[date ordered by Month] = b.[date ordered by Month]) AND
(a.[unit]=b.[unit])
GROUP BY a.Productname, a.unit, a.[date ordered by Month];

CROSSTAB QUERY:

TRANSFORM Sum([Practice Monthly Inventory Final Draft].Expr1) AS SumOfExpr1
SELECT [Practice Monthly Inventory Final Draft].Productname, [Practice
Monthly Inventory Final Draft].unit, Sum([Practice Monthly Inventory Final
Draft].Expr1) AS [Total Of Expr1]
FROM [Practice Monthly Inventory Final Draft]
GROUP BY [Practice Monthly Inventory Final Draft].Productname, [Practice
Monthly Inventory Final Draft].unit
PIVOT [Practice Monthly Inventory Final Draft].[date ordered by Month] In
("August2004","September2004","October2004","November2004","December20
04","January2005","February2005","March2005","April2005","May2005","Ju
ne2005","July2005");

I know that there will probably be a simple and obvious answer to this
question, but I'm afraid that I'm not the person to spot it. Since I've
never really used the SQL view before, I don't know where I've gone wrong.
Any help from anybody out there will be greatly appreciated.

Thank you!

Please provide the DDL (including CONSTRAINTS)
for all of the Tables involved. If the DDL is
not available, please include a well-formatted
text description (monospace-font) of the structures
of each Table, including a description of the
Primary and Foreign Keys (i.e. "relationships").
Please also include some sample data from each table.


Sincerely,

Chris O.
 

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

Top