Wednesday, July 18, 2012 6:11 PM
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
Wednesday, July 18, 2012 6:56 PM
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
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
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
- Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Friday, July 20, 2012 9:32 AM
Thursday, July 19, 2012 5:41 PM
I made a silly mistake. I fixed it and now it works like a charm!