Report/Query Sorting

[skydiver]

I have been presented with a difficult problem and I am not sure how to go
about finding a solution. I hope someone has an idea or two.

I have a report that shows hazardous material products. Several of the
fields are not important, however, there are three fields in which the
report has to be in sorted by.

1.) Health
2.) Flammability
3.) Reactivity.

Each field has a value ranging from 0 - 4. A product could look like the
following; 2-0-0, Health - 2, Flammability - 0, and Reactivity - 0.

The problem is this: The highest value of each row has fall in order from
highest to lowest.

1) 3-3-0
2) 2-4-1
3) 4-3-1

I could sort by column 1 and have the product with the 4 in column 1 at the
top and then sort descending from there, however, sorting on the first
column would result in record 3 followed by 1, and finally record 2.
Unfortunately, Record two has a rating of 4 in column 2, so it has to be
next even though it is in different column.

I sure hope I explained this well enough. Is doing something like this
feasible.

Just another Government mandate.

 

[KARL DEWEY]

Try this ---
SELECT Product.ID, Product.Health, Product.Flammability, Product.Reactivity
FROM Product
ORDER BY
IIf([Health]>=[Flammability],IIf([Health]>=[Reactivity],[Health],[Reactivity]),IIf([Reactivity]>=[Flammability],[Reactivity],[Flammability]))
DESC , Product.Health DESC , Product.Flammability DESC , Product.Reactivity
DESC;

 

[George Nicholson]

Not sure this resolves all potential issues, but here's something to
consider:

Simply add an additional field to your query for MaxRowValue.

MaxRowValue

1) 3-3-0 3
2) 2-4-1 4
3) 4-3-1 4

If sorted by MaxRowValue *then* by Column1, 2 and 3, the record order would
be 3, 2, 1. I think this achieves what you want: group and sort anything
with a 4 before moving on to the 3s, etc.

Same principle could be extended if you needed to 'weight' one factor
differently than another or if you needed to sort by an overall score while
letting individual highs float to the top of ties.

 

[skydiver]

Karl,

Thank you very much for you response. That is exactly what I needed.

Try this ---
SELECT Product.ID, Product.Health, Product.Flammability,
Product.Reactivity
FROM Product
ORDER BY
IIf([Health]>=[Flammability],IIf([Health]>=[Reactivity],[Health],[Reactivity]),IIf([Reactivity]>=[Flammability],[Reactivity],[Flammability]))
DESC , Product.Health DESC , Product.Flammability DESC ,
Product.Reactivity
DESC;

--
KARL DEWEY
Build a little - Test a little

I have been presented with a difficult problem and I am not sure how to
go
about finding a solution. I hope someone has an idea or two.

I have a report that shows hazardous material products. Several of the
fields are not important, however, there are three fields in which the
report has to be in sorted by.

1.) Health
2.) Flammability
3.) Reactivity.

Each field has a value ranging from 0 - 4. A product could look like the
following; 2-0-0, Health - 2, Flammability - 0, and Reactivity - 0.

The problem is this: The highest value of each row has fall in order
from
highest to lowest.

1) 3-3-0
2) 2-4-1
3) 4-3-1

I could sort by column 1 and have the product with the 4 in column 1 at
the
top and then sort descending from there, however, sorting on the first
column would result in record 3 followed by 1, and finally record 2.
Unfortunately, Record two has a rating of 4 in column 2, so it has to be
next even though it is in different column.

I sure hope I explained this well enough. Is doing something like this
feasible.

Just another Government mandate.