Find Nearest Value

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I need some help with finding the "nearest" value and add TRUE/FALSE into an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case, "450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE (and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
 
What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work because it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html
 
Allen,

thanks for the info... this didn't give me the TRUE/FALSE though.

Any ideas as to how I can implement it?

Tom



Allen Browne said:
What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work because it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
I need some help with finding the "nearest" value and add TRUE/FALSE into
an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case, "450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE (and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
Click to expand...
Click to expand...
 
Try entering something like this into the Field row in query design:
([ID] = (SELECT First([ID]) FROM MyTable AS Dupe ORDER BY
Abs(Dupe.[Amount] - 475)))

It may show 0 for false, and -1 for true.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
Allen,

thanks for the info... this didn't give me the TRUE/FALSE though.

Any ideas as to how I can implement it?

Tom



Allen Browne said:
What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work because it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
I need some help with finding the "nearest" value and add TRUE/FALSE
into
an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case, "450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE (and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
Click to expand...
Click to expand...
Click to expand...
 
Allen:

I get an error indicating "agregate values"...

Meanwhile, I searched on the web and came across a function that works
(almost).

IsMin: (SELECT MIN(ABS([Budget]-475)) from Table1)=ABS([Budget]-475)

I would appreciate if you could help me out making some modifications to
it...

Instead of using a field (Budget), I'd rather want to use a calculated field
that needs to be matched to the "475".

So, let's say, OverallBudget = X * Y (while X = 400 and Y = 2)

I though I just could use now:

IsMin: (SELECT MIN(ABS([OverallBudget]-475)) from
Table1)=ABS([OverallBudget]-475)


Unfortunately, that doesn't work.

Allen, I understand that this is not really your answer... still I'd
appreciate if you have any pointers as to how I could utilize a calculated
field in the ABS expression above.

Thanks,
Tom




Allen Browne said:
Try entering something like this into the Field row in query design:
([ID] = (SELECT First([ID]) FROM MyTable AS Dupe ORDER BY
Abs(Dupe.[Amount] - 475)))

It may show 0 for false, and -1 for true.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
Allen,

thanks for the info... this didn't give me the TRUE/FALSE though.

Any ideas as to how I can implement it?

Tom



Allen Browne said:
What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work
Click to expand...
because
it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need some help with finding the "nearest" value and add TRUE/FALSE
into
an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case, "450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE (and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
Click to expand...
Click to expand...
Click to expand...
 
You can replace the calculated field OverallBudget with the actual
calculations, so:
SELECT Min(Abs([X] * [Y] - 475) FROM ...

However, your goal is to select the ID value in the subquery, because you
want to be able to compare it to the ID value in the main query and come up
with a true or false to say whether it is the same as the ID in the record.

If the subquery complained about grouping, try:
([ID] = (SELECT TOP 1 Dupe.ID FROM MyTable AS Dupe
ORDER BY Abs(Dupe.X * Dupe.Y - 475), Dupe.ID))

That assumes:
- MyTable is the name of the table in the main query. Since we are using the
same table in the subquery, we must alias it.
- ID is the name of the primary key field (as in your example).
- X and Y are the fields to be calculated.
Where there is more than one match, TOP 1 will return them all and the
subquery barfs. To prevent that, we add the primary key to the ORDER BY
clause so it has a way to distinguish them.

The subquery now retrieves the single primary key value of the nearest
match. The calculated field then compares it to the ID in the main query. If
it is the same, the expression yields True. Otherwise False.

For more information on subqueries, and other approaches to this, this
article may help:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
Allen:

I get an error indicating "agregate values"...

Meanwhile, I searched on the web and came across a function that works
(almost).

IsMin: (SELECT MIN(ABS([Budget]-475)) from Table1)=ABS([Budget]-475)

I would appreciate if you could help me out making some modifications to
it...

Instead of using a field (Budget), I'd rather want to use a calculated
field
that needs to be matched to the "475".

So, let's say, OverallBudget = X * Y (while X = 400 and Y = 2)

I though I just could use now:

IsMin: (SELECT MIN(ABS([OverallBudget]-475)) from
Table1)=ABS([OverallBudget]-475)


Unfortunately, that doesn't work.

Allen, I understand that this is not really your answer... still I'd
appreciate if you have any pointers as to how I could utilize a calculated
field in the ABS expression above.

Thanks,
Tom




Allen Browne said:
Try entering something like this into the Field row in query design:
([ID] = (SELECT First([ID]) FROM MyTable AS Dupe ORDER BY
Abs(Dupe.[Amount] - 475)))

It may show 0 for false, and -1 for true.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
Allen,

thanks for the info... this didn't give me the TRUE/FALSE though.

Any ideas as to how I can implement it?

Tom



What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work because
it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need some help with finding the "nearest" value and add TRUE/FALSE
into
an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case,
"450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE
(and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
Click to expand...
Click to expand...
Click to expand...
 
Allen,

as always (w/ your suggestion) ... this works great!

Tom


Allen Browne said:
You can replace the calculated field OverallBudget with the actual
calculations, so:
SELECT Min(Abs([X] * [Y] - 475) FROM ...

However, your goal is to select the ID value in the subquery, because you
want to be able to compare it to the ID value in the main query and come up
with a true or false to say whether it is the same as the ID in the record.

If the subquery complained about grouping, try:
([ID] = (SELECT TOP 1 Dupe.ID FROM MyTable AS Dupe
ORDER BY Abs(Dupe.X * Dupe.Y - 475), Dupe.ID))

That assumes:
- MyTable is the name of the table in the main query. Since we are using the
same table in the subquery, we must alias it.
- ID is the name of the primary key field (as in your example).
- X and Y are the fields to be calculated.
Where there is more than one match, TOP 1 will return them all and the
subquery barfs. To prevent that, we add the primary key to the ORDER BY
clause so it has a way to distinguish them.

The subquery now retrieves the single primary key value of the nearest
match. The calculated field then compares it to the ID in the main query. If
it is the same, the expression yields True. Otherwise False.

For more information on subqueries, and other approaches to this, this
article may help:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tom said:
Allen:

I get an error indicating "agregate values"...

Meanwhile, I searched on the web and came across a function that works
(almost).

IsMin: (SELECT MIN(ABS([Budget]-475)) from Table1)=ABS([Budget]-475)

I would appreciate if you could help me out making some modifications to
it...

Instead of using a field (Budget), I'd rather want to use a calculated
field
that needs to be matched to the "475".

So, let's say, OverallBudget = X * Y (while X = 400 and Y = 2)

I though I just could use now:

IsMin: (SELECT MIN(ABS([OverallBudget]-475)) from
Table1)=ABS([OverallBudget]-475)


Unfortunately, that doesn't work.

Allen, I understand that this is not really your answer... still I'd
appreciate if you have any pointers as to how I could utilize a calculated
field in the ABS expression above.

Thanks,
Tom




Allen Browne said:
Try entering something like this into the Field row in query design:
([ID] = (SELECT First([ID]) FROM MyTable AS Dupe ORDER BY
Abs(Dupe.[Amount] - 475)))

It may show 0 for false, and -1 for true.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

thanks for the info... this didn't give me the TRUE/FALSE though.

Any ideas as to how I can implement it?

Tom



What you are asking for is the minimum absolute difference:
SELECT First([Amount]) FROM MyTable ORDER BY Abs([Amount] - 475);

You could use that kind of expression in the context of a query as a
subquery. If you need something in VBA code, DLookup() won't work because
it
lacks the ORDER BY argument, but this replacement will:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need some help with finding the "nearest" value and add TRUE/FALSE
into
an
expression field.

This is the data as pulled from the query:
==========================

ID Amount
1 100
2 99
3 510
4 450
5 21
6 1000
7 375
9 395


I want to find the "Amount" that is nearest to "475".

The "direction" can be either "above" or "below" 475. In this case,
"450"
is TRUE (below amount).


This is the data as I want to see it when qry is executed:
====================================

ID Amount Expression
1 100 False
2 99 False
3 510 False
4 450 True
5 21 False
6 1000 False
7 375 False
9 395 False

If the number to be found was "490", I would expect "510" to be TRUE
(and
450 to be FALSE).


Does anyone know how to do that?


Thanks,
Tom

P.S. As shown above, the records are sorted by ID
Click to expand...
Click to expand...
Click to expand...
 
Back
Top