relation on a function (minimum distantce) of a key

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

Hi all

i have two table that i want to link on the nearest record of a field.
(The other fields are not shown)

table1 table2
code code
20 1
10
100
250
500

In the example 20 should be related to 10 since it is the neareast.
If i have 90 in table 1 i want 100 in table 2
If i have 200 in table 1 i want 250 in table 2
If i have 500 in table 1 i want 500 in table 2
.... and so on
I want ot do that winth a query but i always get an error message
I would like to avoid the use of function like dlookup since they are slow.

Actually i am doing that with function but i want to do it with sql

Best regards
Pierre
 
The way I do that is to build the query conventionally using the query
grid. You know how to do that... drag the field from one table to the
other. Then I make it a Sum query, and ask for the Max value. In your
case, it won't return any records yet.

Then change the query from Design View into SQL view. Scan thru the
SQL code until you find the section where the tables are JOINed with
and "=" sign. Change that sign to "<".

Having done that, you will be unable to display the query in Design
View unless you revert the JOIN back to "=".


Hi all

i have two table that i want to link on the nearest record of a field.
(The other fields are not shown)

table1 table2
code code
20 1
10
100
250
500

In the example 20 should be related to 10 since it is the neareast.
If i have 90 in table 1 i want 100 in table 2
If i have 200 in table 1 i want 250 in table 2
If i have 500 in table 1 i want 500 in table 2
... and so on
I want ot do that winth a query but i always get an error message
I would like to avoid the use of function like dlookup since they are slow.

Actually i am doing that with function but i want to do it with sql

Best regards
Pierre
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Back
Top