Sorting & Ordering question

[CEV]

I have a report. In the Sorting and Grouping option I had originally
selected LastName and Date. This was sorting all records based on the last
name and the date of events for each person was in order under there name.
This presented an issue because two people have the same last name so both
there records were shown under only one name. I have now selected to sort by
Social Security number and the data is seperated for these two people but
the names are no longer in alphabetical order. How can I get this to work?

Thanks,

Chad

 

[Douglas J. Steele]

Sort by all 3 of LastName, Social Security Number and Date. Group on Social
Security Number.

 

[Peter Yang [MSFT]]

Hello Chad,

Besides Doug's suggestion, You may also want to use a query to achieve this
goal. For example, you could use the following query to get the informaiton
you want and then get report directly from the query.

SELECT sid, lastname, firstname, date, value FROM orderdetails GROUP BY
sid, lastname, date, firstname, value ORDER BY lastname, sid, date;

It is not necessary to configure group or sort option in report wizard and
you shall get the proper sequence of the record you want when directly use
the rows of the query.

If you have further questions or comments on the issue, please feel free to
let's know. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Duane Hookom]

Peter,
Are you suggesting that allowing the query to determine the order in the
report is reliable? It has been a near unanimous opinion of MVPs and other
seasoned developers that the sort order in the query is generally ignored in
the report. Are there some situations where the query order is reliable?

Chad also suggested he was grouping based on his statement "shown under only
one name". Any grouping in the Sorting and Grouping dialog will win out over
any sorting in the query or even the OrderBy property of the report.

 

[Peter Yang [MSFT]]

Hello Duane,

I agree that using order in query is not reliable sometimes in report. I
just want to provide another option to Chad in addition to Doug's
suggestion and this usually works for this situation based on my
experience. If Chad try to use the method of query, he shall remove the
sorting/grouping option in report anyway.

Sorry that I did not mention this in my reply and thank you for your great
comments.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[CEV]

I have it set up this way and it is still merging the records for both
people with the same last name under just one of there names. I will
doublecheck it on Monday.

Thanks,

Chad

 

[Duane Hookom]

"under just one of there names" If your group header is the Social Security
Number field then you should not have both people in the same group unless
they have the same ssn.

 

[CEV]

I did finally figure this out. I had the last name as the group header but
included the SSN in sorting so figured it would work. I switched the group
header to SSN and it now works as it should.

Thanks to everyone for the input.

Chad