RE: [MS_AccessPros] Sorting and Grouping on a Report

Art,
Are you aware that you can have multiple sorting and groupin levels in a report?
I would think StatusDate is a bit granular for any grouping but it's your data and we can't see it.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: dbalorenzini@yahoo.com
Date: Thu, 15 Sep 2011 17:46:19 +0000
Subject: [MS_AccessPros] Sorting and Grouping on a Report

I ran into a bit of a dilemma with a report. This is the query behind the report:

SELECT qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name], tlkpRoomCount.RoomCountName, tlkpApplicationStatusType.ApplicationStatusTypeName, tblApplicationStatus.StatusDate, tlkpRoomCount.SortOrder
FROM (qryApplicantExtended INNER JOIN (((tblApplication INNER JOIN tblApplicationReview ON tblApplication.ApplicationID = tblApplicationReview.ApplicationID) INNER JOIN tblApplicationStatus ON tblApplication.ApplicationID = tblApplicationStatus.ApplicationID) INNER JOIN tlkpApplicationStatusType ON tblApplicationStatus.ApplicationStatusTypeID = tlkpApplicationStatusType.ApplicationStatusTypeID) ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID) INNER JOIN tlkpRoomCount ON tblApplicationReview.RoomCountID = tlkpRoomCount.RoomCountID
WHERE (((tblApplicationStatus.ApplicationStatusTypeID)=3))
ORDER BY tlkpRoomCount.SortOrder;

On my report I have it grouped by the RoomCountName which breaks up the report starting with the RoomCountName = "Five". I added a field called SortOrder to tlkpRoomCount so the table looks like this:

RoomCountID RoomCountName SortOrder
1 Five 6
2 Four 5
3 One 1
4 One or two 2
5 Pending 7
6 Three 4
7 Two 3
8 None (Denied) 8

but the issue is I needed the report sorted by oldest StatusDate first which this does right now but I want the grouping in the correct order so its grouped like One, One or Two, Two, etc. basic by the SortOrder field.

Any ideas?

Art Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/