Group by weight

[Annette]

I have a table that collects weights by product. I would like to have
a plus/minus of 10% on the product weight and then list all products
that have weights that fall within the plus/minus.

For example:
Product A has a weight of 80lbs.(72/88 would be the lowest/highest
weight by 10%)
Product B has a weight of 90lbs (82/99 would be the lowest/highest
weight by 10%)
Product C has a weight of 95lbs (85.5/104.5 would be the lowest/
highest weight by 10%)
Product D has a weight of 100lbs (90/110 would be the lowest/highest
weight y 10%)

If I were to run a report for product A listing all other products
that fall within a +/- weight of 10%, I would not have another
product.

If I were to run a report for product B listing all other products
that fall within a +/- weight of 10%, I would list product C.

If I were to run a report for product C listing all other products
that fall within a +/- weight of 10%, I would list product B and
product D.

If I were to run a report for product D listing all other products
that fall within a +/- weight of 10%, I would list product B and
product C.

How do I accomplish this?

 

[John Spencer]

SELECT Product, Weight
FROM Products
WHERE Weight >= (SELECT Weight FROM Products WHERE Product = "A") * .9
AND Weight <= (SELECT Weight FROM Products WHERE Product = "A") * 1.1
AND Product <> "A"

Of course that could be made simpler depending on your actual table and field
names.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County