Dsum criteria problem

[leonhuynh2006]

Dear all bro,



Please take a look at my work. I have been trying many ways to solve the problem but I could not.

The problem in the query, Dsum expression. I would like make the criteria that the "Status" in the table tb_order = yes, but Access says it can not find the name. Please help me. Many thanks.

[Bob Fitz]

Hi

Maybe this what you are looking for:

SELECT Tb_Inventory.PartName, Tb_Inventory.QC1, Tb_Inventory.QC2, Tb_Inventory.QC3, Tb_Inventory.QC4, Tb_Inventory.Side, Tb_Inventory.DK, Sum(Tb_OrderDetails.Quantity) AS SumOfQuantity
FROM Tb_Order RIGHT JOIN ((Tb_Inventory LEFT JOIN Tb_OrderDetails ON Tb_Inventory.MSHH = Tb_OrderDetails.MSHH) LEFT JOIN tb_Toa_Details ON Tb_Inventory.MSHH = tb_Toa_Details.MSHH) ON Tb_Order.OrderID = Tb_OrderDetails.OrderID
GROUP BY Tb_Inventory.PartName, Tb_Inventory.QC1, Tb_Inventory.QC2, Tb_Inventory.QC3, Tb_Inventory.QC4, Tb_Inventory.Side, Tb_Inventory.DK, Tb_Order.Status
HAVING (((Tb_Inventory.PartName)="bac nhat") AND ((Tb_Inventory.QC1)="3t") AND ((Tb_Order.Status)=True));

[leonhuynh2006]

Thanks for fast reply.

However, your code is not what I am looking for. I am looking for Dsum as I need to do some multiple Dsum calculations. I wonder why Access does not find the name of tb_order.Status? Hope you understand what I am saying. Sorry for my english.

[Bob Fitz]

Hi

DSum("Quantity","Tb_OrderDetails","Tb_order.Status =yes")

This can't work because there is no field called "Tb_order.Status" in the table "Tb_OrderDetails".

It may work if you create a query that has both tables in it, and then use that query in DSum instead of "Tb_OrderDetails".
Maybe something like:

DSum("Quantity","NewQuery","Status=yes")

The new query would need to contain both the Quantity and the Status fields.