comparing table values

Discussion in 'Access Table Design' started by Patrick Goodyear, May 8, 2014.

  1. 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 Goodyear, May 8, 2014
    1. Advertisements

  2. 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
    subquery instead:

    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

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, May 8, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.