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

  2. On Thu, 08 May 2014 10:18:17 -0800, Patrick Goodyear <> wrote:

    >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
    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
    none?
    --

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

    http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
    http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
    and see also http://www.utteraccess.com
     
    John W. Vinson, May 8, 2014
    #2
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Greg P.

    comparing lists (columns)

    Greg P., Jul 31, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    119
  2. Colleen Loos

    comparing tables

    Colleen Loos, Aug 6, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    85
    Ayelet
    Aug 6, 2003
  3. ABC

    Populate table with another table values

    ABC, Oct 28, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    72
    Larry
    Oct 28, 2003
  4. wslayton
    Replies:
    1
    Views:
    116
    John Vinson
    Nov 1, 2003
  5. beller
    Replies:
    0
    Views:
    100
    beller
    Jun 15, 2004
  6. Prellyan
    Replies:
    5
    Views:
    113
    Prellyan
    Apr 25, 2005
  7. Robert Robinson

    Inserting values from one table into another table

    Robert Robinson, Oct 21, 2005, in forum: Access Table Design
    Replies:
    6
    Views:
    160
    Pat Hartman
    Oct 24, 2005
  8. Blinds Nottingham
    Replies:
    7
    Views:
    133
    Blinds Nottingham
    Feb 2, 2010
Loading...