SORTING IN QUERY DOESN'T TRANSLATE TO REPORT

[c02homer]

I have a query whose only purpose is to sort a table by user's last
name.

In the report where the query is used, however, due to issues with
grouping by last name (where several people have the same last name and
additionally grouping by user first name did not solve the problem), I
decided to group the output by the unique user id. This id does not
follow the alphabet. So, I created a query to sort the output by user
last name and use the query as the basis for the report. The report,
however, stubbornly sorts by user id. Is there any way to group by
userid but Sort the output by user last name?


It seems like a long time ago there was a way to create a query to do
that - so I'd really really really appreciate any help!!

 

[Rick B]

You can set up multiple grouping and sorting levels in your report.

If you group by UserID, then you can sort by other fields WITHIN that group.

Not sure what you are asking. You would need to give us three or four
examples.

 

[fredg]

I have a query whose only purpose is to sort a table by user's last
name.

In the report where the query is used, however, due to issues with
grouping by last name (where several people have the same last name and
additionally grouping by user first name did not solve the problem), I
decided to group the output by the unique user id. This id does not
follow the alphabet. So, I created a query to sort the output by user
last name and use the query as the basis for the report. The report,
however, stubbornly sorts by user id. Is there any way to group by
userid but Sort the output by user last name?

It seems like a long time ago there was a way to create a query to do
that - so I'd really really really appreciate any help!!

Any sort order in the query is irrelevant in the report.

You must sort the report using the report's Sorting and Grouping
dialog.
In Report Design view, click on View + Sorting and Grouping.

Place the first sort on the first line, followed by the next sort on
the next line, etc.

Try
LastName Ascending
FirstName Ascending
ID Ascending

If you want the report grouped, set the grouping here at the same
time.

 

[c02homer]

Thank you SO much for your response! If what I provide doesn't help,
please feel free to let me know - I need to get this resolved. The
purpose of this report is to list all users and an average of all the
evaluation scores for each user on a single page report. I have
accomplished this by putting the average in the group footer. I
suspect this is what is causing the sorting issue since now the footer
is userid - so if you know of a better way to get averages, please let
me know that, as well.

Here is how the report is currently set up.

Page Header
NAME OF REPORT
Detail
<empty>
UserID Footer
=[userlastname]& ", " & [userfirstname] <plus the other fields that
are averaged and reported on>

Under Sorting and Grouping, I have the following:
GroupBy = userid <Group Footer = Yes>
SortBy = userlastname (ascending)
SortBy = userfirstname (ascending)

The query that provides the data to the report also sorts by
userlastname.

The data in the query looks something like this but is sorted
alphabetically by userlastname:

userid userlastname userfirstname <other fields>
15 Anderson Jim
22 Brooks Jim
1 Morrow Amy
7 Anderson Tom
10 Smith John

My report should output like this:

<Grouping On by userid, sorting by userlastname/userfirstname>
15 Anderson Jim
7 Anderson Tom
22 Brooks Jim
1 Morrow Amy
10 Smith John

What I'm getting, looks like this:

1 Morrow Amy
7 Anderson Tom
10 Smith John
15 Anderson Jim
22 Brooks Jim

I'm happy to provide any other info you need to help me. Thanks!!!!

 

[MGFoster]

Under Sorting and Grouping, I have the following:
GroupBy = userid <Group Footer = Yes>
SortBy = userlastname (ascending)
SortBy = userfirstname (ascending)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, your set up doesn't make sense. It appears you want to have a
separate grouping for each user. OK, but the last name and first name
should identify each user as well as the userid. Since the user ID is
just a number that is equivalent to lastname/firstname, why not group on
the last name & first name, concatenated?

GroupBy = userlastname & "; " & userfirstname (ascending)

Sort that group ascending & your output will look like this:

Anderson Jim
Anderson Tom
Brooks Jim
Morrow Amy
Smith John
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQt3oechKqOuFEgEQLhPwCgi8Vn7eZSdti40wZEvfOD1qpKAI0AnjKj
lWa8qDz70jVzUngrV9pB0Buy
=FD3w
-----END PGP SIGNATURE-----