Sorting problems re queries and forms and a report.

[StargateFan]

Lots of trouble with sorting in a particular contacts db I've made.
Despite having a query that sorts exactly how is needed, forms and
reports based on that query are not sorting consistently despite
careful editing.

I noticed that if I happen to sort the forms and the query differently
from the setup, that that "incorrect" sort stays. That really seems
to defeat the purpose of the query setup. Is there a way to ensure
that the default query takes precedence despite the sort a user might
make on it? I only know how to sort on one field with something like

[Contacts Query].LastName

in "order by" where it should sort by LastName and FirstName but when
one sorts in descending, for example, that goes into the "sort by" box
as

[Contacts Query].LastName DESC

which is against the query, which is sorted by LastName, ascending,
then FirstName, ascending.

I can seem to fix the form if I go in and delete all the "sort by"
entries that get dumped into the form. Then if I save it, then it's
okay. But users might sort and then save and then the problem stays.
It might be fine at one point to disable saving in the form, but even
if one sorts differently and exits the form, when one re-opens it, the
sort is only partially corrected. The query's sort by LastName works
but not by FirstName.

Secondly, the report that is set up with grouping isn't working
properly at all, either. In the sorting/group properties, it's
grouped by LastName, ascending, and then sorted by FirstName, also
ascending. Yet the report only is grouped by alphabetical letter
properly, the entries in the report might be properly grouped by
letter, but they're out of order.

I'm really stumped. I've spent any free time over the weekend looking
at all the properties of the forms/report/query and consulted the
archives but I can't seem to find how to fix these problems.

Any guidance really appreciated. Thank you! D

 

[Ed Robichaud]

One of Access' "features" is filter/sort persistence in forms. Common
solutions to restore the default sort order would be:
to add a command button on the form with the code:
DoCmd.ShowAllRecords
This lets the user control the sort/filter view.

and/or use the form's OnOpen event to run the code
DoCmd.ShowAllRecords
Me.Requery
This resets the default filter/sort whenever the form is opened.

-Ed

 

[John Spencer]

Reports ignore sorting in queries. If you need to Sort or Group in a report,
you must use the reports sorting and grouping dialog to set up the sorting and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

 

[Guest]

Ed covered the forms. I'll take the reports. Another "feature" of Access is
that reports basically throw away any sorting in the underlying query and
resorts the records. To get consistent results you need to set sorting and
grouping in the report. In fact one way to speed up complex reports is to
remove the sorting from underlying queries as the query sort is just a waste
of time and CPU.

P.S. I'm also a Stargate fan.

 

[StargateFanFromWork]

Ed covered the forms. I'll take the reports. Another "feature" of Access is
that reports basically throw away any sorting in the underlying query and
resorts the records. To get consistent results you need to set sorting and
grouping in the report. In fact one way to speed up complex reports is to
remove the sorting from underlying queries as the query sort is just a waste
of time and CPU.

Okay, understood. But what about if we use those same queries for forms?
Is there any point, then, there?
I have tended to make a table, build a query then use that query in
subsequent forms/reports. Perhaps that is the wrong approach?

P.S. I'm also a Stargate fan.

<vbg> Although I only discovered it in 2003 I instantly became a huge fan
both of the original movie, SG-1 _and_ Atlantis! <g> Though I've been too
busy with the last 2 contracts in last year and a half, used to make walls.
If you're interested, take a peek here:
http://www.angelfire.com/art2/hypatia/wallsSimple.html. No thumbnails,
unfortunately. Thanks! D

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Lots of trouble with sorting in a particular contacts db I've made.
Despite having a query that sorts exactly how is needed, forms and
reports based on that query are not sorting consistently despite
careful editing.

I noticed that if I happen to sort the forms and the query differently
from the setup, that that "incorrect" sort stays. That really seems
to defeat the purpose of the query setup. Is there a way to ensure
that the default query takes precedence despite the sort a user might
make on it? I only know how to sort on one field with something like

[Contacts Query].LastName

in "order by" where it should sort by LastName and FirstName but when
one sorts in descending, for example, that goes into the "sort by" box
as

[Contacts Query].LastName DESC

which is against the query, which is sorted by LastName, ascending,
then FirstName, ascending.

I can seem to fix the form if I go in and delete all the "sort by"
entries that get dumped into the form. Then if I save it, then it's
okay. But users might sort and then save and then the problem stays.
It might be fine at one point to disable saving in the form, but even
if one sorts differently and exits the form, when one re-opens it, the
sort is only partially corrected. The query's sort by LastName works
but not by FirstName.

Secondly, the report that is set up with grouping isn't working
properly at all, either. In the sorting/group properties, it's
grouped by LastName, ascending, and then sorted by FirstName, also
ascending. Yet the report only is grouped by alphabetical letter
properly, the entries in the report might be properly grouped by
letter, but they're out of order.

I'm really stumped. I've spent any free time over the weekend looking
at all the properties of the forms/report/query and consulted the
archives but I can't seem to find how to fix these problems.

Any guidance really appreciated. Thank you! D

 

[StargateFanFromWork]

Okay, understood. But what about if we use those same queries for forms?
Is there any point, then, there?
I have tended to make a table, build a query then use that query in
subsequent forms/reports. Perhaps that is the wrong approach?

[snip]

Should have clarified, the queries are used not just for sorting, of course.
The queries pull the information from the database as needed as defined in
the criteria we choose for each field selected. I should have asked the
above question in this manner - should we leave off, then, sorting in these
queries as there are CPU issues involves, it would seem? We can isolate
data we need but leave everything unsorted?

Thanks.

 

[StargateFanFromWork]

Reports ignore sorting in queries. If you need to Sort or Group in a report,
you must use the reports sorting and grouping dialog to set up the sorting and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

Yes, that's what I have. Everything is supposedly sorted properly. Yet
once the grouping is added, the LastName and FirstName fields no longer
display correctly.
They did before the grouping, they don't after. I'm at a loss to know what
to do?

Thanks.

[snip]

[snip]

 

[Guest]

If you use the same queries for both forms and reports AND not seeing
performance problems, I'd just leave well enough alone. I'd only recommend
creating different queries for the forms and reports to fix a performance
problem in the reports. It's probably not a real problem unless you are
dealing with tens of thousands of records anyway.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Okay, understood. But what about if we use those same queries for forms?
Is there any point, then, there?
I have tended to make a table, build a query then use that query in
subsequent forms/reports. Perhaps that is the wrong approach?

[snip]

Should have clarified, the queries are used not just for sorting, of course.
The queries pull the information from the database as needed as defined in
the criteria we choose for each field selected. I should have asked the
above question in this manner - should we leave off, then, sorting in these
queries as there are CPU issues involves, it would seem? We can isolate
data we need but leave everything unsorted?

Thanks.

 

[John Spencer]

Check out the options for grouping, you can choose to do that by just one
character which would put all the As together, but within the A group the names
would not necessarily be in order.

Reports ignore sorting in queries. If you need to Sort or Group in a report,
you must use the reports sorting and grouping dialog to set up the sorting and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

Yes, that's what I have. Everything is supposedly sorted properly. Yet
once the grouping is added, the LastName and FirstName fields no longer
display correctly.
They did before the grouping, they don't after. I'm at a loss to know what
to do?

Thanks.

[snip]

[snip]

 

[StargateFanFromWork]

Check out the options for grouping, you can choose to do that by just one
character which would put all the As together, but within the A group the names
would not necessarily be in order.

Yes, that is what I found out. That is definitely not what is needed. We
need to group but to _also_ be in alphanumeric order within the groups.
This is not good. What can be done??

Thanks.

Reports ignore sorting in queries. If you need to Sort or Group in a report,
you must use the reports sorting and grouping dialog to set up the

sorting
and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

Yes, that's what I have. Everything is supposedly sorted properly. Yet
once the grouping is added, the LastName and FirstName fields no longer
display correctly.
They did before the grouping, they don't after. I'm at a loss to know what
to do?

Thanks.

[snip]

Secondly, the report that is set up with grouping isn't working
properly at all, either. In the sorting/group properties, it's
grouped by LastName, ascending, and then sorted by FirstName, also
ascending. Yet the report only is grouped by alphabetical letter
properly, the entries in the report might be properly grouped by
letter, but they're out of order.

[snip]

 

[John Spencer]

If you need the group A and then sort by the values in the group, add a second
instance of Last Name below the Grouping instance and sort by the Last name (but
don't group by it).

Check out the options for grouping, you can choose to do that by just one
character which would put all the As together, but within the A group the names
would not necessarily be in order.

Yes, that is what I found out. That is definitely not what is needed. We
need to group but to _also_ be in alphanumeric order within the groups.
This is not good. What can be done??

Thanks.

Reports ignore sorting in queries. If you need to Sort or Group in a
report,
you must use the reports sorting and grouping dialog to set up the sorting
and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

Yes, that's what I have. Everything is supposedly sorted properly. Yet
once the grouping is added, the LastName and FirstName fields no longer
display correctly.
They did before the grouping, they don't after. I'm at a loss to know what
to do?

Thanks.

[snip]

Secondly, the report that is set up with grouping isn't working
properly at all, either. In the sorting/group properties, it's
grouped by LastName, ascending, and then sorted by FirstName, also
ascending. Yet the report only is grouped by alphabetical letter
properly, the entries in the report might be properly grouped by
letter, but they're out of order.

[snip]

 

[StargateFan]

If you need the group A and then sort by the values in the group, add a second
instance of Last Name below the Grouping instance and sort by the Last name (but
don't group by it).

<dancing a jig> Yeay! That did it! Wow, I'd never have thought of

Check out the options for grouping, you can choose to do that by just one
character which would put all the As together, but within the A group the names
would not necessarily be in order.

Yes, that is what I found out. That is definitely not what is needed. We
need to group but to _also_ be in alphanumeric order within the groups.
This is not good. What can be done??

Thanks.

StargateFanFromWork wrote:

Reports ignore sorting in queries. If you need to Sort or Group in a
report,
you must use the reports sorting and grouping dialog to set up the sorting
and grouping.

Select View: Sorting and Grouping in your report and set the sorting and
grouping up there.

Yes, that's what I have. Everything is supposedly sorted properly. Yet
once the grouping is added, the LastName and FirstName fields no longer
display correctly.
They did before the grouping, they don't after. I'm at a loss to know what
to do?

Thanks.

[snip]

Secondly, the report that is set up with grouping isn't working
properly at all, either. In the sorting/group properties, it's
grouped by LastName, ascending, and then sorted by FirstName, also
ascending. Yet the report only is grouped by alphabetical letter
properly, the entries in the report might be properly grouped by
letter, but they're out of order.

[snip]

 

[StargateFan]

If you use the same queries for both forms and reports AND not seeing
performance problems, I'd just leave well enough alone. I'd only recommend
creating different queries for the forms and reports to fix a performance
problem in the reports. It's probably not a real problem unless you are
dealing with tens of thousands of records anyway.

Ah, makes sense. Thank you. I'll get the hang of all of this yet.
In the meantime, all this is great guidance! D

Have to say this again: thank you to everyone in this group. I'd be
lost without this fantastic resource! D

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Ed covered the forms. I'll take the reports. Another "feature" of Access
is
that reports basically throw away any sorting in the underlying query and
resorts the records. To get consistent results you need to set sorting and
grouping in the report. In fact one way to speed up complex reports is to
remove the sorting from underlying queries as the query sort is just a
waste
of time and CPU.

Okay, understood. But what about if we use those same queries for forms?
Is there any point, then, there?
I have tended to make a table, build a query then use that query in
subsequent forms/reports. Perhaps that is the wrong approach?

[snip]

Should have clarified, the queries are used not just for sorting, of course.
The queries pull the information from the database as needed as defined in
the criteria we choose for each field selected. I should have asked the
above question in this manner - should we leave off, then, sorting in these
queries as there are CPU issues involves, it would seem? We can isolate
data we need but leave everything unsorted?

Thanks.