SQL query to display amounts with a certain % difference

[TSO456]

I have a column in Access 2002 that contains different amounts of ($) Assets. I need query that can loop through the values in the column and show me the assets amount that have a diffrence of 5%. In the example below the values 100 and 105 should be the result.

$Assets
$300.00
$250.00
$100.00
$105.00
$125.00
$89.00

 

[PHV]

In which order are the assets read ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

Here are some questions for you:


(1) Do you want us to only compare each record with the one that immediately preceeds it?

(2) Do you only want records that vary by exactly 5%, or do you mean "5% or more variance"?

(3) What about the sign of the difference? If the number after 100 had been 95 (instead of 105), then would 95 be included?

 

[TSO456]

(1) I want to compare record (1) to all of the records and then move to the record (2) and compare that to all of the records including record (1) and so on.

(2) I want records that vary by 5% or less.


Thank you
Jill

 

[PHV]

This may be a long run query...
Provided you have a primary key, say ID, you may try this:
SELECT ID, Assets FROM tblAssets A WHERE EXISTS
(SELECT Assets FROM tblAssets B WHERE B.ID<>A.ID
AND B.Assets Between (0.95*A.Assets) And (1.05*A.Assets)
);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

Wow! It sounds like you want to evaluate every potential combination. If a combination has a variance of 5%, then you want to see it. If that is what you want, you will need to evaluate zillions of combinations. It will not be easy conceptually, and it will require alot of processing time.

However, you can do it. Consider this table called "myTable1":
ID value
1 100
2 101
3 200
4 201
5 0


You can put a table into a query twice. Then you can display every potential matchup of field values. In the same query, you can screen out records with zero in the value field (you should screen them out because we don't want to divide by them). This query, called Q_1, does that:

SELECT myTable1!value AS a,
myTable1_1!value AS b
FROM myTable1, myTable1 AS myTable1_1
WHERE ((([myTable1]![value])<>0) AND (([myTable1_1]![value])<>0));

You then need another query. The next query calculates the differences, and selects the records with porportionately large differences:

SELECT
Q_1.a,
Q_1.b,
Abs([a]-) AS c,
[c]/[a] AS d,
[c]/ AS e
FROM Q_1
WHERE ((([c]/[a])>=1/20)) OR ((([c]/)>=1/20));

This last query may not be perfect. The where clause may need some work...perhaps the other readers can comment on it. However, I think that I have illustrated how to handle the problem in general terms.