How to use DSUM in Access Query with multiple criterias and multiple tables

[NDMDRB]

Hello,

I've been searching for 2 days for a solution but didn't find anything, please help me with this small DSUM formula.

I have 3 tables (1st: PurchaseInvoicesT, 2nd: CustomsT) (where I ship goods from 1 country to another, and I can't put them in 1 table because there are 2 different suppliers and so many details in each table
Relationship between these 2 tables is PurchaseID

Here are some fields that matter in my question
1st: PurchaseID, TotalAmount
2nd: CustomsID, PurchaseID, TotalAmount, Notes (Including Customs there is VAT which is in the same invoice but I need to separate it, So I mentioned VAT in Notes in specific Rows)

Lets say I have only 2 Purchase Id's 101 & 102
I'm trying to make a Query to show me PurchaseID (Only 2 Rows 101 & 102), TotalAmount (for each Order), VATAmount (VAT: DSum("Total","CustomsT","Notes='VAT'"))

Here is the main problem
VAT: DSum("Total","CustomsT","Notes='VAT'") This works fine, but it gets me the sum of VAT of all orders
So I need something like that, to get the sum of VAT of each order
VAT: DSum("Total","CustomsT","Notes='VAT'" And [CustomsT]![PurchaseID]=[PurchaseInvoicesT]![PurchaseID])

I've searched and searched and tried a million different ways to write this and nothing works.

Could anyone tell me how to write this DSUM function?

 

[Micron]

Based on your post, I think the following points are relevant:
- you cannot write one aggregate function that involves more than one table
- you can use a query that provides the desired results as the domain/table for an aggregate function
- you should not be storing totals/calculations as a general rule: Allen Browne - Inventory Control: Quantity on Hand

Multiple criteria in domain functions is a common topic; surprised that you didn't find enough guidance there. Perhaps you didn't grasp the method?
If PurchaseID is a number, the concatenation would be more like
DSum("Total","CustomsT","Notes='VAT' And [CustomsT]![PurchaseID]= " & [PurchaseInvoicesT]![PurchaseID])
however, the bold part cannot be used as noted above in first bullet point. So solution is to create a query that relates both tables and use that as the domain. You don't need to include the table/domain in the criteria (CustomsT).