Find closest match between 2 tables

[moishy]

One table (tblContainers) holds container sizes (fields: ID, Type, maxPayload).
Sample data:

ID          Type         maxPayload
1           A1           27,000
2           A2           29,200
3           A3           32,150
4           A4           25,500
5           A5           22,600
6           A6           28,300

Other table (tblMerchandise) holds merchandise weight (fields: ID, Weight)
Sample data:

ID           Weight
1            28,500
2            31,300
3            23,000
4            24,600
5            29,100
6            26,400
7            30,200

I need a query to return the minimum container size required for each record in the tblMerchandise table.

Thank you.

 

[arnelgp]

SELECT id, [weight],(DLOOKUP("[type]","tblContainers","[maxPayLoad]>=" & [weight])) As Type,(DLOOKUP("[maxPayLoad]","tblContainers","[maxPayLoad]>=" & [weight])) As maxPayLoad FROM tblMerchandise;

 

[Grumm]

Don't you get problems like selecting the first record that is bigger ?
For weight 23,000 i get A1... it should be A4.
It should not only select a bigger load but also the closest one i guess

 

[arnelgp]

yes i know, it was an air code.
try this one:

SELECT id, [weight],(SELECT TOP 1 [Type] FROM tblContainers WHERE [maxPayLoad] >= [weight] ORDER BY [maxPayLoad]) As Type,(SELECT TOP 1 [maxPayLoad] FROM tblContainers WHERE [maxPayLoad] >= [weight] ORDER BY [maxPayLoad]) As maxPayLoad FROM tblMerchandise