I suck at this...

[rpeare]

these are my two tables

tblRates
R_ID  Fam_Size  Income_Level
1     1         5000
2     1         2500
3     2         4000

tblCodes
C_ID  Code  Fam_Size  Min_Income  Max_Income
1     Z     1         0           1000.99
2     A     1         1001        2000.99
3     B     1         2001        3000.99
4     C     1         3001        4000.99
5     D     1         4001        5000.99
6     Z     1         0           1500.99
7     A     1         1501        3000.99
8     B     1         3001        4500.99
9     C     1         4501        6000.99
10    D     1         6001        7500.99

What I need to do is look up the CODE field from tblCodes based on the income recorded in tblRates.

I have tried this a few different ways but I'm looking for something more efficient than what I'm currently using

I'm currently running a query that's using the dlookup function and it is taking *forever* which is why I hate using them in queries but the other things I have tried are:

1. cycling through the recordset and looking up values one at a time (this was fairly slow)

2. linking the tables through the family size and putting a formula in calculated field to identify when the income is between the two values, basically a boolean value of 1 or 0 then I'm applying a criteria to that calculated field. This one actually takes less time to run but I have to run two different versions of this one for the 'current' and one for the 'projected' which I want to avoid.



Has anyone got a more efficient way to do this kind of lookup where you are linking on one field and interested in a 'between' value as a secondary lookup.

[CJ_London]

in a query, something like

SELECT R.*, C.Code
FROM tblRates R INNER JOIN tblCodes C ON R.FamSize=C.FamSize AND R.IncomeLevel Between C.MinIncome and C.MaxIncome

if tblCodes is large, recommend famsize, minincome and maxincome fields are indexed

Note you won't be able to see this in the querybuilder because the join cannot be represented there

[ssanfu]

What are the expected Codes for the 3 records in tblRates?

***
An income level of 5000 could be Code D or C.
An income level of 2500 could be Code B or A.
An income level of 4000 could be Code C or B.

Does Family size come into play?
tblRates R_ID #3 has a family size of 2, but tblCodes does not have a Family size of 2.


This is the query I came up with:

SELECT tblRates.R_ID, tblRates.Fam_Size, tblRates.Income_level, 
(SELECT TOP 1 tblCodes.Code FROM tblCodes WHERE tblRates.Income_level>=[tblCodes].[Min_Income] And tblRates.Income_level < [tblCodes].[Max_Income];) AS Code
FROM tblRates;

Note the "TOP 1" in the subquery. (see the the *** above)
Note the Family size is not taken into account.

R_ID	Fam_Size	Income_level	Code
1	1	5000	D
2	1	2500	B
3	2	4000	C

[rpeare]

Sorry I buggered my table it should be as below.

I'll work through your suggestions (both of you) and get back when I've got something that works the way I want.

tblCodes
C_ID Code Fam_Size Min_Income Max_Income
1 Z 1 0 1000.99
2 A 1 1001 2000.99
3 B 1 2001 3000.99
4 C 1 3001 4000.99
5 D 1 4001 5000.99
6 Z 2 0 1500.99
7 A 2 1501 3000.99
8 B 2 3001 4500.99
9 C 2 4501 6000.99
10 D 2 6001 7500.99

[rpeare]

Ajax, I took your recommendation in the end. It cut about 5 minutes out of my processing time so thanks. My final query is below with example data if anyone else is looking for this type of solution in the future.

MasterIndividualID	FamilySize	AdjustedGross	CalcRider	ProjRider
999999	6	$7,500.00
999998	1	$803.00
999997	4	$0.00
999996	2	$3,635.93
999995	1	$2,099.50
999994	2	$0.00
999993	3	$5,807.57

P_Rider	P_FamilySize	P_MinSalary	P_MaxSalary
Z	1	$0.00	$1,485.99
A	1	$1,486.00	$1,563.99
B	1	$1,564.00	$1,641.99
C	1	$1,642.00	$1,719.99
D	1	$1,720.00	$1,797.99
E	1	$1,798.00	$1,875.99
F	1	$1,876.00	$1,953.99

FamilySize	MinSalary	MaxSalary	Rider
1	$1,486.00	$1,610.00	A
1	$1,611.00	$1,735.00	B
1	$1,736.00	$1,860.00	C
1	$1,861.00	$1,985.00	D
1	$1,986.00	$2,110.00	E
1	$2,111.00	$2,235.00	F

The final query was:

UPDATE (tblSFRiderConfig INNER JOIN tblSFTestMatrix ON tblSFRiderConfig.FamilySize = tblSFTestMatrix.p_familysize) INNER JOIN qrySF_GOSH_Base ON tblSFRiderConfig.FamilySize = qrySF_GOSH_Base.FamilySize SET tblSFRiderConfig.CalcRider = [rider], tblSFRiderConfig.ProjRider = [p_rider]WHERE (((tblSFRiderConfig.AdjustedGross) Between [p_minsalary] And [p_maxsalary]) AND ((tblSFRiderConfig.adjustedgross) Between [minsalary] And [maxsalary]));

I'm going through the detail now but it appears to be doing exactly what I wanted. I hadn't considered the method you suggested so thanks again!

[CJ_London]

I had wondered about putting the between part in the WHERE part of the query, but have always believed it to be quicker when included as part of the join. Sounds like there is little or no difference