Solved Query from Crosstab Query Error Message because of NullStock

[ahmad_rmh]

I have made a query from Crosstab Query to make the "Stock Summary Balance" but if one of the column Heading balance is not available then it shows error message becuase of null stock otherwise the query is working with balances.

Error Message: The Microsoft office database engine does not recognize '[OpeningStock]' as a valid field name or expression
(Image is attached herewith)
Note: This message is coming because of the null stock,

How could I avoid this error to happen? If there is other way to get Stock summary balance in crosstab query then let me know please.


The detail of the queries are as under;

a. Columns in Query made from corsstab query: ItemsPK, ItemsListName, Opening, Purchases, Transfers, Adjustments, StockBalance, AvgUsage
(Images are attached herewith)

b. Columns in Crosstab Query: ItemsPk, ItemsListName, Stock, SumofStock_In_Out
(Images are attached herewith)

c. Union Query:

SELECT ItemsPK, ItemsListName, Sum(Stock_In_Out) AS SumOfStock_In_Out, TranDate, TranTypePK, "OpeningStock" AS Stock
FROM qryTransactionsExtended
GROUP BY ItemsPK, ItemsListName, TranDate, TranTypePK
HAVING (((TranDate)<=DateSerial(Year(Date()),Month(Date()),0)))
ORDER BY ItemsPK

UNION ALL

SELECT ItemsPK, ItemsListName, Sum(Stock_In_Out) AS SumOfStock_In_Out, TranDate,TranTypePK, "NetPurchases" AS Stock
FROM qryTransactionsExtended
GROUP BY ItemsPK, ItemsListName, TranDate, TranTypePK
HAVING (((TranDate) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((TranTypePK)<=2))

UNION ALL

SELECT ItemsPK, ItemsListName, Sum(Stock_In_Out) AS SumOfStock_In_Out, TranDate, TranTypePK, "NetTransfers" AS Stock
FROM qryTransactionsExtended
GROUP BY ItemsPK, ItemsListName, TranDate, TranTypePK
HAVING (((TranDate) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((TranTypePK)>=3 And (TranTypePK)<=5))

UNION ALL

SELECT ItemsPK, ItemsListName, Sum(Stock_In_Out) AS SumOfStock_In_Out, TranDate, TranTypePK, "MonthlyAdjustments" AS Stock
FROM qryTransactionsExtended
GROUP BY ItemsPK, ItemsListName, TranDate, TranTypePK
HAVING (((TranDate) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((TranTypePK)=6));

(Image is attached herewith)



Kindly suggest the solution.

 

[theDBguy]

One possible approach is to make sure the Crosstab query always has all possible columns included by using the In() clause or the Column Headings property.

 

[ahmad_rmh]

One possible approach is to make sure the Crosstab query always has all possible columns included by using the In() clause or the Column Headings property.

Sorry, I didn't get the idea, kindly tell me how to do....

 

[MajP]

I am not sure if that is the problem, but here is the way to do what @theDBguy is suggesting using the query designer

How to Fix Missing Fields in a Crosstab Query in Access

www.quackit.com

In SQL this creates a PIVOT IN clause.

Again I am not clear on your issue, but one thing before doing a crosstab is to use NZ() around fields that could return null.

 

[ahmad_rmh]

Thanks @MajP and @theDBguy,

Got the solution from the link which the Maj has shared, thanks

 

[theDBguy]

Thanks @MajP and @theDBguy,

Got the solution from the link which the Maj has shared, thanks

Glad to hear you got it sorted out. @MajP and I were happy to assist. Good luck with your project.

 

[CJ_London]

@ahmad_rmh - try a simpler query - based on the db I sent you a while ago, avoids the need for a union uqery

TRANSFORM Sum([Quantity]*[multiplier]) AS SM
SELECT tblItems.ItemPK, tblItems.ItemsListName
FROM tblItems INNER JOIN (tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK) ON tblItems.ItemPK = tblTransactions.ItemsFK
GROUP BY tblItems.ItemPK, tblItems.ItemsListName
ORDER BY Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments")
PIVOT Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments") In ("Opening Balance","NetPurchases","NetTransfers","MonthlyAdjustments");

and to include the current stock as well - I don't know what the average usage calculation is (perhaps netTransfers/day(date())?) but should be easy enough to do in a form or report

TRANSFORM Sum([Quantity]*[multiplier]) AS SM
SELECT tblItems.ItemPK, tblItems.ItemsListName, Sum([Quantity]*[multiplier]) AS CurrentStock
FROM tblTranTypes INNER JOIN (tblItems INNER JOIN tblTransactions ON tblItems.ItemPK = tblTransactions.ItemsFK) ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK
GROUP BY tblItems.ItemPK, tblItems.ItemsListName
ORDER BY Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments")
PIVOT Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments") In ("Opening Balance","NetPurchases","NetTransfers","MonthlyAdjustments");

 

[ahmad_rmh]

@ahmad_rmh - try a simpler query - based on the db I sent you a while ago, avoids the need for a union uqery

TRANSFORM Sum([Quantity]*[multiplier]) AS SM
SELECT tblItems.ItemPK, tblItems.ItemsListName
FROM tblItems INNER JOIN (tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK) ON tblItems.ItemPK = tblTransactions.ItemsFK
GROUP BY tblItems.ItemPK, tblItems.ItemsListName
ORDER BY Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments")
PIVOT Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments") In ("Opening Balance","NetPurchases","NetTransfers","MonthlyAdjustments");

and to include the current stock as well - I don't know what the average usage calculation is (perhaps netTransfers/day(date())?) but should be easy enough to do in a form or report

TRANSFORM Sum([Quantity]*[multiplier]) AS SM
SELECT tblItems.ItemPK, tblItems.ItemsListName, Sum([Quantity]*[multiplier]) AS CurrentStock
FROM tblTranTypes INNER JOIN (tblItems INNER JOIN tblTransactions ON tblItems.ItemPK = tblTransactions.ItemsFK) ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK
GROUP BY tblItems.ItemPK, tblItems.ItemsListName
ORDER BY Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments")
PIVOT Switch([trandate]<=Date()-Day(Date()),"Opening Balance",[trantypePK]<=2,"NetPurchases",[trantypePK]<=5,"NetTransfers",True,"MonthlyAdjustments") In ("Opening Balance","NetPurchases","NetTransfers","MonthlyAdjustments");

Thanks a lot CJ, It has worked a lot and now no need of union query and its related queries.

 

[CJ_London]

happy to help. If you want to regularly group your transactions into 'net' movements you can always add a another grouping table to simplify the query further (removes the need for the switch function) - I do this for financial systems where I want to group nominal codes into things like sales, cost of sales, overheads etc., often several levels deep for views at different levels of granularity.