Sorting - Help! (sorting fields associated with primary key)

A

accessgrits

I have an inherited customer trade show loaner equipment database where I'm
trying to pull from several tables information to sort in different reports
(one by parent serial, one by child serial, and one listing all serials by
customer).

I'm having difficulty getting the report to sort by customer.

The table that lists the customer names has an autonumber primary key set,
and no matter how much I try the customer names will only sort by the order
they were entered using the autonumber (I've tried to change the index, I've
tried to change the sort order in the report and query that pulls the
information) - nothing seems to work.

Any ideas? Please help!

Thanks!
Accessgrits
 
R

Rick B

The sort order of a report is determined by the Sorting and Grouping options
in the report design. What do you have there?
 
A

accessgrits

That's the other thing I have tried, but it sorts it again by the autonumber
instead of the text even though the fields pulled are solely the text fields
that list the customer names. Although it will group it, it will only sort
it by the autonumber.

Thanks for helping!
 
R

Rick B

How can it group it but sort differently?

If you are sorting and grouping by the autonumber (unique) then you would
have a group header for every single record. It can't group by person and
sort bay autonumber.

In the following example, each person has multiple records. The report is
sorted and grouped by person, then the details are further sorted by
autonumber.

Jones, Paul
123014 06/06/74 Green
123256 07/14/82 Blue
Smith, John
123456 12/30/68 Green
123479 01/14/65 Red
123510 05/14/72 Blue

Please give us an example of what your report is printing and what you would
prefer to see.
 
A

accessgrits

It can group and sort differently because it groups by the order it was
entered but sorts by the autonumber - the order it was entered. So grouping
is not the problem, the problem is how it is sorted.

For example:

In my customer table it might look like the following:

Autonumber Customer Name
1 Apple
2 Grape
3 Orange
4 Bananas
5 Kiwi

When I try to sort it, it will only sort by the primary key (the autonumber)
even though I've tried to change the index, the sorting options in the query,
and the sorting/grouping options - but even after all that it still will look
as it is listed above. I can only temporary change the sorting to show it
alphabetically like this:

Autonumber Customer Name
1 Apple
4 Bananas
2 Grape
5 Kiwi
3 Orange

The order it was entered above is how it will sort it in all - the table,
the query, and the report.

Any ideas?

Thanks!
 
D

Douglas J Steele

Are you saying that you've got Customer Name as the first field in the
report's Sorting and Grouping dialog, and that it's not sorting correctly?
For reports, that's the only thing that matters: changing the index or
changing the sort order of the query will not affect the report.


You're not expecting the Autonumbers to change to match the sort order, are
you?
 
R

Rick B

You say below you can "temporarily change it to sort alphabetically". Well,
that is what you want. You want to temporarily change the sort (in your
report).

The order in the table is irrelevant.
 
A

accessgrits

Yes, in the Sorting and Grouping dialog box in the report I am running I have
the items listed in this order:

Field/Expression Sort Order
1. Customer Name Ascending (also set to group)
2. Parent Serial # Ascending (also set to group)
3. Child Serial # Ascending - this is not grouped

Even with this, I still get it sorted, not alphabetically (which I want),
but it is sorted by the order it was entered (by using the primary key
autonumber in the customer table).

What do you think?
 
A

accessgrits

I can't temporarily sort it in the report, I can only get it to temporarily
sort in the table by changing the sort. As soon as I save it, exit out of
the table, and reenter to look at it again, it does not hold the sort it goes
back to sorting by the autonumber even though I've also tried to change the
sort with the index, once I exit out it moves the primary key to the top and
resorts it again by the primary key.

Nothing I can do to change the sort in the report will allow it to sort
alphabetically, it will only sort by the primary key (autonumber).

Any other ideas? I really appreciate you time!
 
R

Rick B

AGAIN,
Please show us exactly what is in your sorting box and EXACTLY what your
output looks like. Attach a screen print.

If you have the name as your first sort item, then the report will sort by
name. Period.

Are you using a report and a Subreport? If so, then you would need to tell
us the options for both of them.
 
R

Rick B

Also, stop worrying about the sort in the table. You should not care at all
how the data is sorted in the table. Don't even look at the table again; it
has nothing to do with the report sorting.
 
D

Douglas J Steele

Your Customer Name field wouldn't happen to be a Lookup field in your table,
would it?

If so, be aware that the name isn't actually stored in that table: the ID
is.
 
A

accessgrits

Please e-mail me at karaisagrits@(DO_NOT_SPAM)yahoo.com, I can't attach
screen shots on this message board.

Again, thanks for your help.
 
Top