Inventory Query criteria misses one Item Product's units

[cimoli]

I cannot figure out why my date oriented criteria does not return all opening inventory records.

This is hard to explain. I've attached a simple 2 table example and 1 query.
The question revolves around the QryInventory and the last field called TransactionDate.
I wanted all records from tblItem. You will see a criteria that I thought would use EACH Item's
UnitsopeningDate field as the starting date for an items transaction.

EG there is a product in the tblItemn (my friend should be using a number ItemID. i will change it later)
called ..... Wild Oil Oreng .... it has an opening units of 149 on 3/1/2013.

Notice the other dates are the same for last time inventory was taken for those items.

Turning to the QryInventory, I was hoping that for Wild Oil Oreng, that the date 3/1/13 would
be used for any inventory transactions after or equal to 3/1. (there are none.)
The QryInventory just ignores Wild Oil Oreng and it should not since there are 149 units in ending inventory.

I narrowed the problem down to the Criteria in QryInventory.
So this is the question. How can i get Wild Oil Oreng to show on the datasheet view of QryInventory ?
The criteria line seems to use 1/1/2013 only as the basis. Why? I thought each Item could stand on its own UnitsOpeningDate.


SELECT tblItem.ItemID, tblItem.ItemDescription, tblItem.UnitsOpeningDate, tblItem.UnitsOpening AS [Units Opening], Sum(tblInventoryTransactions.UnitsReceived) AS [Units Received], Sum(tblInventoryTransactions.UnitsSold) AS [Units Sold], Sum(tblInventoryTransactions.UnitsOther) AS [Units Other], Nz([Units Opening])+Nz([Units Received])-Nz([Units Sold])+Nz([Units Other]) AS [Units Ending]
FROM tblItem LEFT JOIN tblInventoryTransactions ON tblItem.ItemID = tblInventoryTransactions.ItemID
WHERE (((tblInventoryTransactions.TransactionDate)>=[UnitsOpeningDate] Or (tblInventoryTransactions.TransactionDate) Is Null))
GROUP BY tblItem.ItemID, tblItem.ItemDescription, tblItem.UnitsOpeningDate, tblItem.UnitsOpening
ORDER BY tblItem.ItemID;

sorry if this sounds too complicated to ask. if it is, just delete my post.
thanks. file sample link to mediafire attached.

 

[cimoli]

In summary, the problem is the 3/1/2013 UnitsOpeningDate in tblitem for the Wild Oil Oreng product.
If I change the 3/1/2013 to 1/1/2013, the product's inventory will properly show in QryInventory.

I need something extra in my QryInventory criteria to have the criteria look at each Item line.

thanks.

 

[cimoli]

I am trying to say that i want to use different UnitsOpeningDate.
We may take inventory at different times of the year for just certain Items.
When I change the opening qty and date in tblItem, i would want past tblInventoryTransactions
for that item code to be ignored by QryInventory.

Right now, QryInventory seems to require all tblItem dates to be the same period.
In QryInventory, I have a criteria line which probably needs a tweak.

If I use 3/1/2013 for one NEW Item in tblItem, and leave 1/1/2013 for the rest of the Items,
the QryInventory criteria ignores the new Item. that is the problem.
I want to see all opening figures from tblItem.