Sorting a Query

[theseandavis]

Hey there,

Im trying to sort some data here beyond just ascending or descending.
Basically, I have a list of data in three columns. Let's call them A,
B, and C. Column A has 23 different types of data and must be grouped
together (ie, the 1st of 23 within A is together then the 2nd of A,
etc). Column B has 3 different types - Forecast, Plan, and Actual.
Column C has up to 277 different types.

What I need is:
A1..B1..C1
A1..B2..C1
A1..B3..C1
A1..B1..C2
A1..B2..C2
A1..B3..C2

...again, where B is forecast, plan, and actual. Essentially All of the
A's grouped, then C's, and then B1, 2, and 3.

Any help?

 

[Guest]

Offhand, I think this will do what you want, although I did not attempt to
test it first:

SELECT A, B, C FROM TableName
ORDER BY A, C, B


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[theseandavis]

Thank you! That's worked for the most part. Through this response I
have now learned SQL with the help of a big fat book of course...

Is there any way to overcome what happens when there are two entries of
the same kind?

eg.
A1..B1..C1
A1..B2..C1
A1..B3..C1
A1..B1..C2
A1..B1..C2
A1..B2..C2
A1..B2..C2
A1..B3..C2
A1..B3..C2

.... in this instance there are two items listed for C2 with the same
name (so, two groups of 3), and rather than display 'Forecast Plan
Actual Forecast Plan Actual', it instead displays 'Forecast Forecast
Plan Plan Actual Actual'.

 

[Guest]

Try this:

SELECT A, B, C
FROM TableName
GROUP BY A, B, C
ORDER BY A, C, B;

If you want to prevent these duplicates from being entered in the first
place, then you can set a unique multi-field index on the three fields A, B &
C in table design view.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________