Need help with ORDER BY SQL syntax

[phoang]

Hello,

I have built a crosstab query to calculate a sum for a specified date. ACCESS automatically populates the query alphabetically by the "CourtTimeSaved" column, however, I want a different ORDER BY list. How may I do that?

TRANSFORM Count([Court Time Saved].ID) AS CountOfID
SELECT [Court Time Saved].CourtTimeSaved, Count([Court Time Saved].ID) AS Total
FROM [Court Time Saved]
GROUP BY [Court Time Saved].CourtTimeSaved
PIVOT Format([Date(s)],"mmm") In ("Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar");

The options for "CourtTimeSaved" are:
- Morning Chambers application
- Special Chambers application
- Trial
- Unknown
- None

Thank you!

 

[revans]

How would you like your query to be ordered?

Rich

 

[phoang]

By the list of options above:
- Morning Chambers application
- Special Chambers application
- Trial
- Unknown
- None

Thank you.

 

[revans]

Add a field to your table called something useful like "OptionOrder" then put the order you'd like to see your options. Sort by this field in your query.

hth,

Rich

 

[phoang]

I am not very advanced with Access so I'm not sure how to do that extra field in the query. Is there an easier way right within the report?

Thanks.

 

[revans]

I'm using Access 2003. In the database window right-click your table and choose "Design View". You'll see the list of the table's fields. Below that list in the Field Name column type "OptionOrder" (without quotes), or whatever you feel you'd like. In the Data Type field choose "Number". Save the table and view the table's data (Menu item View then Datasheet View). Fill in the order you'd like each item to be ordered with (1,2,3,4...).

Find your query. Right-click it in the database window and choose "Design View". You'll see the QBE (Query by example) window. Scroll all the way to the right until you have an empty column. In the "Field" (top line) field select "OptionOrder" (or whatever you named the field). In the "Sort" field select "Ascending".

If you have other fields that have a sort order you may want to position the OptionOrder to the left of them as Access orders query fields from left to right in the QBE, meaning it sorts by the left-most field's sort order, then the next field, then the next and so on.

hth,

Rich