Lose records when sort order is changed

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

Guest

The records in my table have a unique number associated with each (ID). Each
also has fields for expiration date and client name. If I sort on ID, my
report contains all the records. If, however, I sort by expiration date or
client name, I lose the records where more than one record contains the same
exp date or client name. I cannot find where one makes a selection to allow
or not allow duplicates so don't know how to correct this. Also, the MS
instructions for undoing a sort order state that one should simply delete the
field names from the Field/Expression column in Sorting/Grouping, but the
software doesn't allow it. You can delete the names, but not the
"Ascending/Descending" choice, and you aren't allowed to continue without
re-entering something in the Field/Expression column. Thank you for any help.
 
The records in my table have a unique number associated with each (ID). Each
also has fields for expiration date and client name. If I sort on ID, my
report contains all the records. If, however, I sort by expiration date or
client name, I lose the records where more than one record contains the same
exp date or client name. I cannot find where one makes a selection to allow
or not allow duplicates so don't know how to correct this. Also, the MS
instructions for undoing a sort order state that one should simply delete the
field names from the Field/Expression column in Sorting/Grouping, but the
software doesn't allow it. You can delete the names, but not the
"Ascending/Descending" choice, and you aren't allowed to continue without
re-entering something in the Field/Expression column. Thank you for any help.
Click to expand...

How and where are you doing the sorting? If you're doing so in the
Sorting and Grouping dialog OF A REPORT, then Access is doing exactly
what you're asking: sorting the records, and *grouping* those records
which share a common value.

Try instead using a Query - NOT a Totals query, no Group Bys anywhere
- with a sort (or multiple sort fields, left to right in the query
grid).

John W. Vinson[MVP]
 
Ken said:
Report's will normally
ignore the sort order of a query. Its often said that they will *always* do
so, but that's not entirely true in my experience. Nevertheless a report
should always be sorted by means of its internal sorting and grouping
mechanism.
Click to expand...

Is that a true statement?

Consider the below quote (with spelling corrections) from a Usenet
thread regarding the infamous 'DECIMAL sort bug':
[Jamie Collins] the results are predicable rather than
'wildly inaccurate' as claimed and the workaround is simple and
reasonable (e.g. do the sorting in the recordset)
Click to expand...

[Albert D.Kallal (Access MVP)] Sorting in a recordset
is of NO use when running a report.
Click to expand...

(source:
http://groups.google.com/group/microsoft.public.access.gettingstarted/msg/15b67bcc32b972c3).

I may have got the wrong end of the stick but I got the impression
there was no way of sorting the resultset for the report other than
using ORDER by in the Query object. Otherwise why didn't Albert say
something like, "Sorting in a recordset is of NO use when running a
report but sorting in the report itself is just fine"?

I don't know anything about reports. Can you (or anyone) tell me
whether a column of type DECIMAL that includes negative values be
correctly sorted into descending order in a report?

Thanks in advance.

For background details, see:

BUG: You may notice an incorrect sorted order when you sort the
negative decimal values in descending order in Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;837148

Jet 4.0 DECIMAL type: sort order bug
http://groups.google.com/group/microsoft.public.access/msg/9245b50947e9839f

Jamie.

--
 
Ken said:
As regards sorting Decimal data type values in descending order in a report
the bug bites here too and the values are ordered incorrectly.
Click to expand...

Ken,
Many thanks for your reply.

Jamie.

--
 
Thank you for your advice, Ken. You were correct that I had the controls in
the header rather than the detail section and moving them corrected the
problem. Your instructions for deleting entries in the sort dialogue were
also helpful. And finally, you were also correct about the query--I had tried
this solution and the report is unaffected by the sort order of the query.
Thanks so much! Sherry S.
 
Back
Top