Selecting rows that are similar

[Sharp]

Hi

Consider the following table:

field1 field2
10 15
20 500
30 40
40 100
50 55

I want to select rows where the data value is similar (within +/- 10%).
The percentage constraint is important as it could change in the future.
I expect the following query output.

field1 field2
10 15
30 40
50 55

Any help appreciated.

Cheers
Sharp

 

[Guest]

This is the query you want:

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2)>=([Table1].[Field1])-(([Table1].[Field1])*0.1)) AND
((Table1.Field2)<=([Table1].[Field1])+(([Table1].[Field1])*0.1)))
WITH OWNERACCESS OPTION;

where your table is called table1. Change the two 0.1's to change the
percentage. 10% actually returns the following.

field1 field2
50 55

Hope this helps

Rico

 

[Sharp]

Hi

Consider the following table:

field1 field2
10 15
20 500
30 40
40 100
50 55

I want to select rows where the data value is similar (within +/- 10%).
The percentage constraint is important as it could change in the future.
I expect the following query output.

field1 field2
10 15
30 40
50 55

Any help appreciated.

Cheers
Sharp

Just tried:


....
Where field2 = (field1 + 0.1*field1) or (field1 - 0.1*field1)

why doesn't this work?

Cheers
Sharp

 

[Guest]

Its not an OR function, it needs to be an AND, your missing the table
definitions and you need to have great/less than commands in there otherwise
it will only return exact values.

Did the query i posted not work?

rico

 

[Douglas J. Steele]

Just tried:


...
Where field2 = (field1 + 0.1*field1) or (field1 - 0.1*field1)

why doesn't this work?

As Rico pointed out, you need to use inequalities, not equalities.

Not only that, but your syntax is wrong anyhow: you need to repeat the
field2 name.

Try:

Where field2 Between (field1 - 0.1*field1) And (field1 + 0.1*field1)

 

[Barry-Jon]

Sharp,

It doesn't work because you are specifying that it must be equal to 10%
less than or 10% more than. What you want is where the value is
between 10% less and 10% more than as in rico's example.

 

[Sharp]

Sharp wrote in message

As Rico pointed out, you need to use inequalities, not equalities.

Not only that, but your syntax is wrong anyhow: you need to repeat the
field2 name.

Try:

Where field2 Between (field1 - 0.1*field1) And (field1 + 0.1*field1)

Ah I see now... but I'm still getting a minor problem.
It appears that the Between keyword is not inclusive.
How can this be fixed?

Cheers
Sharp

 

[Barry-Jon]

Sharp,

I have tried both of the following and they work inclusively;

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2) Between [Field1]-([Field1]*0.1) And
[Field1]+([Field1]*0.1)));

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2)>=[Field1]-([Field1]*0.1) And
(Table1.Field2)<=[Field1]+([Field1]*0.1)));

Can you try this and let us know how you get on?

Regards

Barry-Jon

 

[Sharp]

Sharp,

I have tried both of the following and they work inclusively;

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2) Between [Field1]-([Field1]*0.1) And
[Field1]+([Field1]*0.1)));

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field2)>=[Field1]-([Field1]*0.1) And
(Table1.Field2)<=[Field1]+([Field1]*0.1)));

Can you try this and let us know how you get on?

Regards
Barry-Jon

Thanks Barry, they both worked.

Cheers
Sharp