'sub-sort' within a sort

[mapondera]

I have the fields: product, value, year
For one product there are many years. The value of a product changes each year. have all three fields in one table (Yes, spreadsheet-like but because of how I have to use the database with other people in my team, it has worked so far.)

I want a query that will:
1. sort entries by the value in descending order for the year 2002 but still keep the same product clustered together.
2. within each cluster of a given product sort by year in ascending order

e.g.
PRODUCT YEAR VALUE
milk 2002 $4
milk 2003 $5
milk 2004 $5
bread 2002 $3
bread 2003 $5
eggs 2002 $2
eggs 2003 $1
eggs 2004 $3

 

[Dreamboat]

Do it using the report wizard and the summary sorting options.

 

[mapondera]

I have tried this using the 'grouping/sorting' property in design view and the report wizard. I don't know how to specify to group like products together but sort first by the value where year = 2002

 

[Pat Hartman]

Maybe I'm missing something but it looks like a simple order by clause:

Select ...
From ...
Order By ProductName, YearProduced;

The difference is if you sort by productName, the result will be in alphabetical order:

PRODUCT YEAR VALUE
bread 2002 $3
bread 2003 $5
eggs 2002 $2
eggs 2003 $1
eggs 2004 $3
milk 2002 $4
milk 2003 $5
milk 2004 $5


BTW, using reserved words like "year" as names for your objects will lead to nothing but trouble. Changing this name is advised.