Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Query join based on Range

    I have two table. E.G.

    Table1
    ID
    AcctNum
    System


    Table2
    Description
    AcctMin
    AcctMax

    Query
    AcctNum
    System
    AcctMin
    AcctMax


    I want to update the Table1.System with Table2.Description based on the AcctNum failing in a Range using AcctMin to AcctMax.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I can't see any obvious way to do this besides writing code to do the update because of a missing relationship between T1 and T2.

    Perhaps some of the SQL geniuses here can?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Non-equi join:

    ...
    FROM Table1 LEFT JOIN Table2 ON (Table1.AcctNum Between Table2.AcctMin and Table2.AcctMax)
    Paul

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    This didn't return any results.

    SELECT tbl_charter_remittance_data.[Invoice Number], tbl_datech_systems.[System Sh], tbl_datech_systems.AcctStart, tbl_datech_systems.AcctEnd
    FROM tbl_datech_systems LEFT JOIN tbl_charter_remittance_data ON (tbl_charter_remittance_data.[Invoice Number] BETWEEN tbl_datech_systems.AcctStart AND tbl_datech_systems.AcctEnd);

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I wouldn't expect "Invoice Number" to work, since you earlier mentioned AcctNum. If that's not it, can you post a sample db?
    Paul

  6. #6
    Join Date
    Sep 2006
    Posts
    162
    Sorry, thats my mistake, works as it should.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •