Join table based on minimum values

[jessica_webb]

Hello,

Apologies that I'm not able to provide any sample script, but I have no idea where to begin with this and can't find anything similar in the forums.

I would like to do the following in the script, as I believe it is more effective...

I have a very large table called TABLE_INFO with the following key fields (there are many other fields in addition to these):

Qual_pred	Is_A	Is_B	Is_C	Prior_AB	Prior_C
Spanish	1			28	34
Spanish	1			33	38
Spanish	1			20	27
English			1	43	54
English			1	29	27

I then have another table (TABLE_NEW) which I would like to join to TABLE_INFO, with the following fields:

Qual_pred	Min	Max	Outcome
Spanish	16	20	33
Spanish	21	25	37
Spanish	26	30	40
Spanish	31	35	42
English	18	21	28
English	22	25	36
English	26	29	38

I need to do the following for every row in TABLE_INFO.

1) Look at the 'Qual_pred'.

2) If it is 'Is_A' or 'Is_B', use value 'Prior_AB' / If it is 'Is_C', use value 'Prior_C'

3) Look in TABLE_NEW and find matching 'Qual_pred'

4) If value 'Prior_AB' or 'Prior_C' (depending on step 2), falls between 'Min' and 'Max', return 'Outcome' value to TABLE_INFO

I would hope to end up with the following:

Qual_pred	Is_A	Is_B	Is_C	Prior_AB	Prior_C	Outcome
Spanish	1			28	34	40
Spanish	1			33	38	42
Spanish	1			20	27	33
English			1	43	23	36
French		1		17	29

Any help appreciated - even just a hint as to what direction I should go in!

Many thanks in advance,

Jess

[jessica_webb]

Paul, that helps enormously - thank you so much!

I now have a single table of data, with the Outcomes matched in correctly.

For some reason I'm still getting duplicates in my worksheet table, showing nulls for outcome, e.g:

Qual_pred	Is_A	Is_B	Is_C	Prior_AB	Prior_C	Outcome
Spanish	1			28	34	40
Spanish	1			28	34	-
Spanish	1			33	38	42
Spanish	1			33	38	-
Spanish	1			20	27	33
Spanish	1			20	27	-
English			1	43	23	36
English			1	43	23	-

But I'm guessing that could be down to a fault somewhere else in my script... Hopefully it won't affect my calculations.

Thanks once again for your help.

Best wishes,

Jess