leafsrock100
Registered User.
- Local time
- Yesterday, 21:42
- Joined
- Jul 29, 2010
- Messages
- 17
Hi,
I am trying to create a monthly inventory report such that I can query the beginning inventory, purchases and sales between a user-inputted date range on a form. The problem is that during the specified date range, customers may not have ordered a certain product. Hence, when the "Purchases" query is run, products that were purchased in the date range show up while unpurchased products do not, and hence have blank records.
I would like to show a query with all the products and their purchases, if any, and their purchase price, if any.
When I create the Inventory report, the blank records hinder me from calculating the ending inventory for all products. Is there anyway to convert the blank records to '0' so that I can sum up the purchases during the date range? I have tried the nz() function and isnull() but I can't seem to make it work.
The following is the SQL statement of the Purchases query:
SELECT [Inventory Transactions].ProductID, Nz(Sum([QtyReceived]),0) AS Purchases, Format(Round(Sum(nz([RawUnitPrice]*[QtyReceived],0))/Sum(nz([QtyReceived],0)),2),"$0.00") AS PurchasePrice
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].TransactionDate)>=[Forms]![Filtered Inventory Summary Date Range]![BeginDate] And ([Inventory Transactions].TransactionDate)<=[Forms]![Filtered Inventory Summary Date Range]![EndDate]) AND (([Inventory Transactions].TransactionDescID)=2))
GROUP BY [Inventory Transactions].ProductID;
I include both purchases and sales in a "Inventory Transactions" table and distinguish them by having a "TransactionDescID". I also include the unit price each product is purchased at and if there are multiple purchases a month, I perform a weighted average cost calculation to determine the price for the ending inventory.
Any help would be greatly appreciated,
leafsrock100
I am trying to create a monthly inventory report such that I can query the beginning inventory, purchases and sales between a user-inputted date range on a form. The problem is that during the specified date range, customers may not have ordered a certain product. Hence, when the "Purchases" query is run, products that were purchased in the date range show up while unpurchased products do not, and hence have blank records.
I would like to show a query with all the products and their purchases, if any, and their purchase price, if any.
When I create the Inventory report, the blank records hinder me from calculating the ending inventory for all products. Is there anyway to convert the blank records to '0' so that I can sum up the purchases during the date range? I have tried the nz() function and isnull() but I can't seem to make it work.
The following is the SQL statement of the Purchases query:
SELECT [Inventory Transactions].ProductID, Nz(Sum([QtyReceived]),0) AS Purchases, Format(Round(Sum(nz([RawUnitPrice]*[QtyReceived],0))/Sum(nz([QtyReceived],0)),2),"$0.00") AS PurchasePrice
FROM [Inventory Transactions]
WHERE ((([Inventory Transactions].TransactionDate)>=[Forms]![Filtered Inventory Summary Date Range]![BeginDate] And ([Inventory Transactions].TransactionDate)<=[Forms]![Filtered Inventory Summary Date Range]![EndDate]) AND (([Inventory Transactions].TransactionDescID)=2))
GROUP BY [Inventory Transactions].ProductID;
I include both purchases and sales in a "Inventory Transactions" table and distinguish them by having a "TransactionDescID". I also include the unit price each product is purchased at and if there are multiple purchases a month, I perform a weighted average cost calculation to determine the price for the ending inventory.
Any help would be greatly appreciated,
leafsrock100