Solved Lookup values in a range

[AnilBagga]

I have 2 sample tables as in the enclosed DB file

1. tblItemMaster which has PartNo and a DFL value for each item
2. tblValueAdd which have a Value add for a min and max value of DFL

What is the most efficient way to build a query where I can get the ValueAdd for each item in the tblItemMaster?

I have done this in multiple ways but not sure what is the best and most efficient way.

Would appreciate some advise

 

[pbaldy]

How about:

SELECT tblItemMaster.ItemCode, tblItemMaster.DFL, tblValueAdd.ValueAdd
FROM tblItemMaster INNER JOIN tblValueAdd ON (tblItemMaster.DFL <= tblValueAdd.DFLMax) AND (tblItemMaster.DFL >= tblValueAdd.DFLMin);

Note it can't be represented in design view, only SQL view.

 

[AnilBagga]

How about:

SELECT tblItemMaster.ItemCode, tblItemMaster.DFL, tblValueAdd.ValueAdd
FROM tblItemMaster INNER JOIN tblValueAdd ON (tblItemMaster.DFL <= tblValueAdd.DFLMax) AND (tblItemMaster.DFL >= tblValueAdd.DFLMin);

Note it can't be represented in design view, only SQL view.

Thanks.

 

[pbaldy]

Happy to help!

 

[AnilBagga]

Happy to help!

Hi pbaldy,

I tried to be adventurous and tried to do a 3 way left join between 2 tables. I got over the syntax errors with some searching but the values to be fetched are not showing - "Rate" and "Waste" in qryTapeValueAdd. Where am I going wrong?

 

[arnelgp]

see Query2.

 

[AnilBagga]

see Query2.

 

[AnilBagga]

It cannot be done in one query without Dlookup? Dlookups I hear slow down queries

 

[pbaldy]

You have a join between Category and RM, but they don't contain any matching values so you'll never pull a value from the ValueAdd table:

 

[pbaldy]

The color fields don't appear to match up either.

 

[AnilBagga]

You have a join between Category and RM, but they don't contain any matching values so you'll never pull a value from the ValueAdd table:

View attachment 90879

With the IIF condition the values match. I made a dummy data of the TapeCodeMastertbl with results of the IIF condition as values and the Query works. This means the results of the IIF condition are not considered as values of the table in the JOIN statements.

So I guess a 2 stage query as suggested by ArnelGP with Dlookup's in the second query is the only solution.

 

[arnelgp]

Query3 uses sub-query instead of Dlookup().

 

[AnilBagga]

Query3 uses sub-query instead of Dlookup().

Thank you Arnel

 

[arnelgp]

you're welcome Anil.