Blank query records between date range (1 Viewer)

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

Outer join to the inventory table to bring in the items that have not been transacted.

You also need to look at this bit because there is danger of a divide by zero error.
))/Sum(nz([QtyReceived],0)

Sorry if I wasn't clear before but I meant that the 'Purchases' query is based off the 'inventory transactions' table. I have a separate 'sales' query that is also based off the same table.

The thing is, the query sometimes returns nothing, not null or empty values, just no records. For example, let's say the table looks like this:

Date. ProductID. Desc Qty. price
5/15/2010. A. Purchase 40. $7
6/20/2010. A. Sales. 10. $10

If I 'purchase' query from 5/1/2010 to 5/30/2010, the first record appears but the sales query would be blank. If I 'purchase' query from 6/1/2010 to 6/30/2010, the purchase query would be blank while the sales query would show the second record. Basically, Can I change a nonexistent record to a '0' so that I can calculate the ending inventory? D

As I said above. Join the Inventory table to the Transactions table.

Set the join properties to "Show all records from Inventory and matching records from Transaction". This will return a record for an untransactied ProductID with Null in the Quantity field. Use NZ to convert this to zero.

Thanks for the quick reply, Galaxiom

I got it to work! Sorry, I'm pretty new to Access and I didn't understand what you were asking me to do in your previous post

Thanks again

Hi Galaxiom,

I tried to query the Purchases/Sales as you stated from above. I started to enter past data starting from January 2010 and the query worked for that month.

However, the subsequent months did not query all the products, only the products that had a purchase/sale in that month. i.e. if a product had a sale in Feb., it showed up in the purchase query with a zero and vice versa. if a product did not have either a sale/purchase in Feb, it would not show up.

Any help would be greatly appreciated

You would need to join in a list of months in the same way the missing transactions were added with the outer join.

You can work with months by using the Month() and Year() functions to derive a Year and Month grouping on the dates in the Transaction table. Outer Join this to a table of all the months and years you are interested in.

I don't understand how I would use the Month() and year() functions. Would I need a separate dates table? How would I incorporate the day of the month? or would I even have to?

Why does it not work the way I outer joined the products and transactions table?