Alphabetization crazies.

[Sue]

I have a report based on a query that draws from 3 tables - a Client table,
an Interventions table, and a Join table. (It's a many-to-many relationship,
so the Join table contains the "many" Intervention field.)
The interventions are rather lengthy, so I've got a field in the
Interventions table that abbreviates each intervention. This is what I want
in my report.
I've grouped/sorted in my report so that we alphabetize first by client,
then by abbreviated intervention. The interventions aren't properly sorted
alphabetically, though. The client names are abbreviated, and the
interventions beneath each client seem to be alphabetized in 2 different
series, A -> Z, then A -> Z.
Any clues?
I've gone back to the query & even to the original table - in each instance,
the abbreviated interventions are properly alphabetized.

 

[John Spencer]

In a report, you should set the sorting and grouping by using the Sorting and
Grouping dialog (Menu View: Sorting and Grouping ...).

A report ignores any sorting done in the underlying table or query. Often
that does not make a difference and the records will be sorted as you want -
but that is basically a coincidence. So in report design view, open up the
Sorting and Grouping dialog and set up the desired sorting.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue]

Well, the odd thing is that I used the sorting & grouping box in the report,
and when it was sorted wrong (as in original post), I went back to the query,
then to the table, and tried sorting in those locations as well. I don't
understand what I'm doing wrong!

 

[John Spencer]

IF it is sorting wrong, but you have the sorting set up correctly, it is time
to look at the data.

Do some fields have leading spaces or a hidden character?

Did you use a combobox or listbox as a LOOKUP for a field in the table design?
If so, what you see is not necessarily what is stored. The data stored
could be a number that is in the lookup table and what you see could be the
string text associated with the number. When you sort, things are sorted by
hidden number value.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue]

I went to every single table in the database - no combo boxes anywhere.
I do have combo boxes for the same field in both the query and the report.
Could this cause the problem?
And no - no leading characters.
Sheesh.

I appreciate your time & any suggestions you may offer.

 

[John Spencer]

If you have a combobox in the query, then the associated field does is
using the lookup combobox "mis-Feature" in the table.

If you open the table in design view and click on the field, the lookup
tab of the field properties should show
Display Control: Combobox

Row Source Type: Table/Query

Row Source: A table name or a query
Bound Column: 1 (or some other number) which tells you which column is
being used to get the value that is STORED in your table

Column Widths: if you see data in here and one of the values is 0 then
that column is hidden. If the zero corresponds to the bound column
number, then the bound column is hidden.

If this is not the case then I am stumped. If it is the case, note the
name of the table in the row source. It will either be a table name or
if it is a query, you will need to get the table name from the query.

Once that is determined you will need to add that table to your query
and join the relevant tables and fields.

Good Luck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Sue]

Thanks for the pointers - just what I needed to hear.