Sorting report by country, first all US, than all other countries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report in Access 2003 which lists data by countries. I need the
output in the report to print as follows. In outher words, I need it to list
US countries first, the all other countries in alphabetical order.

Country Serial number
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111

Instead, it currently prints like this:
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444

Any assistance would be greatly appreciated.
 
Create a field for sorting only, and do not display this field. One way to do
this would be to sort by alphabetical order, but use a switch in your sort
field like:
switch([country_name]="United States","AAAUnited States",true, [country_name])

This will put United States at the top of your list, and all other country
names will keep the same order.
 
1. Create a query into this table.
In a fresh column in the FieldRow, enter this expression:
IsUSA: [Country] = ("United States")
Use this query as the RecordSource of your report.

2. In report design view, open the Sorting And Grouping box (View menu.)
In the first row, choose the IsUSA field.
In the second row, choose the Country field.

Explanation: The expression returns True for USA, False for all other
countries (and Null if country is blank.) True sorts before False.
 
In the reports Sorting and Grouping Dialog, try entering these two lines

Field/Expression : Sort Order
---------------------------------------------
=Country = "United States" : Ascending
Country : Ascending

You may have to switch the Sort Order on the first line to Descending. Try
the proposed setup first.
 
Thank you so much for your assistance. I appreciate your help.

Duane Hookom said:
You must include the "=" at the beginning of the function/expression.

--
Duane Hookom
MS Access MVP
--

slaze said:
Create a field for sorting only, and do not display this field. One way to
do
this would be to sort by alphabetical order, but use a switch in your sort
field like:
switch([country_name]="United States","AAAUnited States",true,
[country_name])

This will put United States at the top of your list, and all other country
names will keep the same order.
Click to expand...
Click to expand...
 
Thank you so much for your assistance. Now the report works exactly as I
needed.

Thanks again!

Allen Browne said:
1. Create a query into this table.
In a fresh column in the FieldRow, enter this expression:
IsUSA: [Country] = ("United States")
Use this query as the RecordSource of your report.

2. In report design view, open the Sorting And Grouping box (View menu.)
In the first row, choose the IsUSA field.
In the second row, choose the Country field.

Explanation: The expression returns True for USA, False for all other
countries (and Null if country is blank.) True sorts before False.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LDMueller said:
I have a report in Access 2003 which lists data by countries. I need the
output in the report to print as follows. In outher words, I need it to
list
US countries first, the all other countries in alphabetical order.

Country Serial number
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111

Instead, it currently prints like this:
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444

Any assistance would be greatly appreciated.
Click to expand...
Click to expand...
 
LDMueller said:
I have a report in Access 2003 which lists data by countries. I need the
output in the report to print as follows. In outher words, I need it to list
US countries first, the all other countries in alphabetical order.

Country Serial number
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111

Instead, it currently prints like this:
Australia 3,464,444
Australia 3,844,444
Bulgaria 2,424,444
Canada 3,222,111
United States 4,444,444
United States 4,444,445
United States 4,444,544
United States 4,445,444
Click to expand...



In the report's Sorting and Grouping window (view menu) set
it to first sort on the expression:
Country <> "United States"
and set the second sort to the field Country
 
Back
Top