Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Access 2007: Complicated join of a query and table

Answered Access 2007: Complicated join of a query and table

  • Wednesday, July 18, 2012 6:11 PM
     
     
    Sign In to Vote
    0

    I am trying to join a table and query...

    • QueryX contains the following fields (approx 3000 records): ID, Value1High, Value1Low, Value2High, Value2Low
    • TableX contains the following fields (approx 350 reconds): ID, Value1, Value2

    I want to form a query based on QueryX and TableX where QueryX.ID is returned for each instance of TableX.ID where TableX.Value1 is between QueryX.Value1High and QueryX.Value1Low AND TableX.Value2 is between QueryX.Value2High and QueryX.Value2Low.

    So, there may be zero or multiple QueryX.ID returned for each TableX.ID.

    Any ideas? This may take some SQL that is beyond my knowledge.


    • Edited by K_Linville Wednesday, July 18, 2012 6:12 PM
    •  
     

All Replies

  • Wednesday, July 18, 2012 6:56 PM
     
      Has Code
    Sign In to Vote
    0

    Does this do what you want?

    SELECT TableX.ID, QueryX.ID AS QID AS TID 
FROM TableX, QueryX 
WHERE TableX.Value1 Between QueryX.Value1Low And QueryX.Value1High AND 
TableX.Value2 Between QueryX.Value2Low And QueryX.Value2High

    Regards, Hans Vogelaar

     
  • Wednesday, July 18, 2012 8:24 PM
     
     
    Sign In to Vote
    0

    Hans, thank you for the fast reply!

    The SQL you suggested is similiar to what I was trying with no luck. Even with using your SQL I get zero records returned, even though I know there should be about 27 records of TableX.ID that falls within a range of values in QueryX.

    It seems that there should be a some type of look up function rather than a where clause using 'between'. I used EXCEL to verify that records should exist and I used the following formula:

    =INDEX($B$2:$B$3000,MATCH(1,INDEX(($C$2:$C$3000>=M2)*($D$2:$D$3000<=M2) * ($I$2:$I$5000>=N2)*($J$2:$J$3000<=N2),0,1),0))

    Where columns C,D,I,J contain the value ranges, column B is the ID field for the range and columns M,N contain the values to be looked up. Therefore, every set of values in columns M,N was cross referenced in columns C,D,I,J to determine if the values fall within the range. If the set did, then the ID in column B was returned.

    This is the same thing I want to do, but in Access and using a Table containing the values to be cross reference and a Query which contains the ranges and ID field to be returned.

     
  • Wednesday, July 18, 2012 8:31 PM
     
     Answered
    Sign In to Vote
    0

    Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Access forum. You can attach files up to 250 KB to a post there.

    Regards, Hans Vogelaar

     
  • Thursday, July 19, 2012 5:41 PM
     
     
    Sign In to Vote
    0

    I made a silly mistake. I fixed it and now it works like a charm!

    Thank you!!