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?

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.
Similar Threads
  1. Greg P.

    comparing lists (columns)

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

    comparing tables

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

    Populate table with another table values

    ABC, Oct 28, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    93
    Larry
    Oct 28, 2003
  4. wslayton
    Replies:
    1
    Views:
    139
    John Vinson
    Nov 1, 2003
  5. beller
    Replies:
    0
    Views:
    127
    beller
    Jun 15, 2004
  6. Prellyan
    Replies:
    5
    Views:
    125
    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:
    180
    Pat Hartman
    Oct 24, 2005
  8. Blinds Nottingham
    Replies:
    7
    Views:
    145
    Blinds Nottingham
    Feb 2, 2010
Loading...