Need some help on Dlookup

[MD12]

Hi all. I am very new to access and I need quite abit of help here. I have 2 tables (Table 1 = Quicksearch and Table 2 = Positions)

Table 1 (Quicksearch)
Customer(Text)__ ID1(Text)____% P(Number)
S11111_________01111-000___ 1
S22222_________02222-000___ 0.5

Table 2 (Positions)
Customer(Text)__ ID2(Text)____ % hold(Number)
S11111________ 01111-000____ 1
S11111________ 02222-000____ 1
S22222________ 03333-000____1
S22222________ 04444-000____2

I am trying to create a query to display "% hold" found in Table 2 using the criteria of customer, ID1 and ID2. Additionally, if customer matches in both table but ID1 and ID2 does not match, it will return a "0" for % hold

I tried under criteria of Query "=DLookUp(Nz"[% hold]","Quicksearch","[ID2]=" & "ID1",0) but it doesn't work. Would really appreciate if someone can help me on this

End result of my query should look like this using the format of Table 1 with an additional column(% hold)
Column 1 Column 2 Column 3 Column 4
Customer(Text)__ID1(Text)____ % P(Number)__ % hold(Number)
S11111________ 01111-000___ 1____________ 1
S22222________ 02222-000___ 0.5___________ 0

 

[namliam]

You dont want a DLookup, you want a join... a left join to be more exact.

Review
https://www.google.nl/url?sa=t&rct=...zYf4a5-bZ1aC1tk4YP3QVKA&bvm=bv.86475890,d.ZWU

https://www.google.nl/url?sa=t&rct=..._xM_Km_BYnvf_dZimhsdMag&bvm=bv.86475890,d.ZWU

Let me know if you still have trouble

 

[plog]

First, you should only use alphanumeric characters in table/field names. I would eliminate the spaces and percent symbols in your field names.

Now for your issue, you are going to need a subquery based on Positions. This subquery will determine the % hold value for each Customer. That SQL would be this:

SELECT Positions.Customer, IIf(Max([% hold])=Min([% hold]),Max([% hold]),0) AS HoldPercent
FROM Positions
GROUP BY Positions.Customer;

Paste that into a query, save it. Then, create a new query using it and Quicksearch to get the final output.