I have the following code set to run a query and give me the sum of inventory movements of a particular type. The problem is that I need it to show a value for all items from tblItems that have a material type = "RM" as shown. When I use the where clause on movement type "262" it only gives me totals for items that have that movement type. To reiterate: I need it to show a 0 value for items that didn't have any of that movement type that day. CAn anyone give me a hand here? I have tried using some fallbacks like the Nz() function but it doesn't seem to be working...
SELECT tblItems.Item, Sum(tblRawMaterialDataDump.Quantity) AS InvReturnFromProd
FROM tblItems LEFT JOIN tblRawMaterialDataDump ON tblItems.Item = tblRawMaterialDataDump.Item
WHERE (((tblItems.MaterialType)="RM") AND ((tblRawMaterialDataDump.MovementType)="262"))
GROUP BY tblItems.Item;
I am sure this is something simple I am missing...