unequal links data between a table and query

D

DavidC

I've runa query that returns results to 4 decimal palces and want to be able
to lookup or link these results to a 2 column table where the first column
shoudl eb used as the refercence dta for the query results but that column's
data only has data every at intervals of 0.05 whereas the query results are
more exact. Can someone show me how to have unequal links or use a lookup
functin - I couldn't workout how to use the Dlookup function for this
purpose...thanks
 
J

John Vinson

I've runa query that returns results to 4 decimal palces and want to be able
to lookup or link these results to a 2 column table where the first column
shoudl eb used as the refercence dta for the query results but that column's
data only has data every at intervals of 0.05 whereas the query results are
more exact. Can someone show me how to have unequal links or use a lookup
functin - I couldn't workout how to use the Dlookup function for this
purpose...thanks
Click to expand...

A "Non Equi Join" will work here. Let's say you have qryResult with
the calculated field Res, and a table Ref with fields Reference and
Value.

Create a query joining the two tables, joining Ref to Value. (This
won't get any results except by chance of course).

Go into SQL view and edit the query JOIN clause from

ON qryReault.Res = Ref.Value

to

ON qryResult.Res >= Ref.Value AND qryResult.Res < Ref.Value + .05

This query won't display in query design view - you'll need to use the
SQL window - but it should work (unless of course the value of Res is
less than the smallest Value or greater than the largest Value + .05).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
D

DavidC

John

Thank you for answering - my delayed response is due to a hardware problem -
this is my first time to use the discussion forum, it's really very helpful.
Just one thing though, the query I run produces several rows of results which
I then want to link to the table as previously noted. Your suggestion
(assuming I made the correct adjustment to the SQL - a big assumption)
returns the corresponding value in the table for the lowest of the original
query results. I was hoping to get values from the table that closest match
each result from the original query. Again, I know I'm not explaining this
well but any additional help would really be appreciated. Thank you.
 
J

John Vinson

John

Thank you for answering - my delayed response is due to a hardware problem -
this is my first time to use the discussion forum, it's really very helpful.
Just one thing though, the query I run produces several rows of results which
I then want to link to the table as previously noted. Your suggestion
(assuming I made the correct adjustment to the SQL - a big assumption)
returns the corresponding value in the table for the lowest of the original
query results. I was hoping to get values from the table that closest match
each result from the original query. Again, I know I'm not explaining this
well but any additional help would really be appreciated. Thank you.
Click to expand...

"closest" would use a range around the number. Could you please post
your actual SQL, and perhaps a few records from the range table and a
couple of examples of which record you would want to match? Examples
at the extremes of the ranges would be particularly helpful.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top