# comparing table values

P

#### Patrick Goodyear

Hi, all!

I have what I will call a lookup table. The table contains four fields:
an Autonumber ID field, a FreqLo field, a FreqHi field, and a
FreqSubband field.

In a second table, I have sites with a value in a field called
NewTxFrequency. What I need to do is find the values of FreqLo, and
FreqHi, in the first table, that NewTxFrequency falls between, and
assign the value of FreqSubband from the first table into a field in the
second table.

Any and all help is, very much appreciated.

Kind Regards,

Patrick

J

#### John W. Vinson

Hi, all!

I have what I will call a lookup table. The table contains four fields:
an Autonumber ID field, a FreqLo field, a FreqHi field, and a
FreqSubband field.

In a second table, I have sites with a value in a field called
NewTxFrequency. What I need to do is find the values of FreqLo, and
FreqHi, in the first table, that NewTxFrequency falls between, and
assign the value of FreqSubband from the first table into a field in the
second table.

Any and all help is, very much appreciated.

Kind Regards,

Patrick

You can use what's called a "Non Equi Join" to do this. Try

UPDATE [second table]
SET [the second table field] = [lookup table].[FreqSubband]
FROM [second table] INNER JOIN [lookup table]
ON [second table].[NewTxFrequency] >= [lookup table].[FreqLo]
AND [second table].[NewTxFrequency] <= [lookup table].[FreqHi]

This might not be an updateable query; if that's the case you may need a

UPDATE [second table]
SET [the second table field]
SELECT [FreqSubband] FROM [lookup table]
WHERE [second table].[NewTxFrequency] >= [lookup table].[FreqLo]
AND [second table].[NewTxFrequency] <= [lookup table].[FreqHi])

However... what if there are two or more qualifying NewTxFrequency values? Or
none?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups: