Selecting records above and below target value

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

Hello,

I have a table containing a list of businesses and some general info. One
of the fields in my table is TotalAssets. What I want to do is create a
query that selects the 10 rows above and below a TargetAsset parameter that
is input by the user.

Currently I am using two "SELECT TOP 10..." queries, one that looks for the
10 just above and the other that looks for the 10 below the TargetAssets. I
use a UNION query to join the two statements together. It works just fine,
however it causes an extra bit of complexity to my query design and vba
programming and makes my project much more difficult because of a specific
way I need my query to output. Not to mention any change I would have to
make to the statement needs to be made twice to accomidate both SELECT
statements.

Is there an easier way to do this by using only one SELECT statement? Any
thoughts are appreciated.

-Mike C
 
Hi Mike,

I doubt that it's possible, because you need to sort by the difference
between each value and the target value to be able to use the "TOP 10"
clause. Although you could use ABS(Difference) to return values both above
and below the target (and use a TOP 20 rather than top 10), you could not
sort the returned values in the same query, and you also could not guarantee
that you would get 10 above and 10 below - you might get 17 above and 3
below, or 4 above and 16 below, depending on the exact values in your data.
I think that a Union of the two sets is a necessity here.

It might be possible to do it via a custom function, built using 2
recordsets and then combined. But in a plain-vanilla SELECT query, I don't
think so.

Doing it via a Union query shouldn't be that tricky, though, since you can
build the Union query to use a single parameter (most easily got from an
unbound textbox control on a form) in both parts; there shouldn't be any
need to re-write the query (via code) each time it's used.

HTH,

Rob
 
What you are doing sounds perfectly reasonable method to get the results you
want.

It does sound like it's getting to the stage where it might be useful to use
a temporary table to store the primary key values of the 10 records just
above the TargetAsset, and the 10 records below. Your code would then:
- Execute a DELETE query statement to empty the temp table.
- Execute an INSERT query statement to add the upper 10.
- Execute another INSERT query statement to add the lower 10.
- Use an INNER JOIN between the temp table and the main table as the source
for your output.

That may be easier to trace/debug than what you are doing, and might execute
faster.
 
And a follow-up:

Here's a Union query, which uses a standard parameter prompt, to return the
three nearest values below and three nearest values above the target. It
uses UNION ALL in case the value entered is an exact match for a value in
the table (without that, the fact that Union queries eliminate duplicates
would lead to only 5 records, rather than 6, being returned in that case).

(SELECT TOP 3 tblValues.Value, [Value]-[Target] AS Diff
FROM tblValues
WHERE (((tblValues.Value)>=[Target]))
ORDER BY [Value]-[Target])
UNION ALL
(SELECT TOP 3 tblValues.Value, Abs([Value]-[Target]) AS Diff
FROM tblValues
WHERE (((tblValues.Value)<=[Target]))
ORDER BY Abs([Value]-[Target]))
ORDER BY [Value];

Again, HTH,

Rob
 
Thanks for your responses,

Well at least now I know I'm probably doing it the right way. I do like the
idea of using the temporary table at this point because in addition to the
TOP 10 I have to LEFT JOIN the main table to the secondary table multiple
times based on different group numbers. That may help some alleviate some of
the over-complex code I've resorted to in order to get this one simple report
to behave as it should.

Thanks again, you guys are awesome

Mike C
 
Here is a query that I use to get a range of records above and below a
specific value. It may give you some ideas. It also may be faster or
slower than what you have already developed.

This one was used to give me N records alphabetically either side of a
specific value. So you would need to change the RecordValue parameter
to a number value - probably Currency.

PARAMETERS PlusOrMinus Short, RecordValue Text ( 255 );
SELECT FAQ.fSubject
, (Select Count(*) FROM FAQ as F1
WHERE F1.fSubject < FAQ.fSubject) AS Rank
, (Select Count(*) FROM FAQ as F2
Where f2.fSubject <[RecordValue]) AS Expr1
FROM FAQ
WHERE (Select Count(*)
FROM FAQ as F1
WHERE F1.fSubject < FAQ.fSubject)
Between (Select Count(*) FROM FAQ as F2
WHERE f2.fSubject <[RecordValue])-[PlusOrMinus]
And (SELECT COUNT(*) FROM FAQ as F2
WHERE F2.fSubject<[RecordValue])+[PlusOrMinus])
ORDER BY FAQ.fSubject;
 
Back
Top