Match number in Table A with closest matching number in Table B

[charlie010019]

At first glance I thought this would be simple but having a tough time figuring this out.
I have a tblA_Amounts of Ingredients and Amounts (in ml) and a tblB_Scoops of Scoops & ScoopSize (in ml). For each ingredient amount I want to join on closest matching scoop from tbl_Scoops. E.g. for Blackberries I need about 170ml. I don't have a 170ml scoop but closest is a 150ml scoop.

What query/SQL could I use to get closest matching scoop for all the Ingredients/Amounts on tblA_Amount? See my (manual) mock up qryDesiredResult...

[ranman256]

after you join the 2 tables, get the difference:

Diff: ABS(A.Amt - B.Amt)

sort ascending

or you can use that query to get DMIN() to get the least different.

[CJ_London]

to add to ranmans comments, sort ABS(A.Amt - B.Amt) ascending and use TOP 1 which might return two values if the difference is the same both ways. Say quantity is 175 - it would return both 150 and 200, so order by both difference (ascending) and scoop size (ascending if you want the smaller size, or descending if you want the larger size)

[charlie010019]

Cheers for those ideas guys - I'm trying to put them into practice in an SQL statement. Sorry to say I'm not doing so well! Could you hammer a statement you think would work best?