sort crosstab query properly 1

[THWatson]

Using Access 2003 with 2000 format

Here's my crosstab query

PARAMETERS [Forms]![frmDateSelector]![txtStartDate] DateTime, [Forms]![frmDateSelector]![txtEndDate] DateTime;
TRANSFORM Count(tblAttendance.Present) AS CountOfPresent
SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblAttendance.TypeOfMeeting, Count(tblAttendance.MeetingDate) AS [Meetings Attended]
FROM tblMembers LEFT JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
WHERE (((tblAttendance.TypeOfMeeting)="Regular Meeting") AND ((tblMembers.Status)<>"Deceased" And (tblMembers.Status)<>"Transferred Out") AND ((tblAttendance.Present)=Yes))
GROUP BY tblMembers.MemberID, [LastName] & ", " & [PreferredName], Switch(Month([MeetingDate])=10,1,Month([MeetingDate])=11,2,Month([MeetingDate])=12,3,Month([MeetingDate])=1,4,Month([MeetingDate])=2,5,Month([MeetingDate])=3,6,Month([MeetingDate])=4,7,Month([MeetingDate])=5,8,Month([MeetingDate])=6,9,Month([MeetingDate])=7,10,Month([MeetingDate])=8,11,Month([MeetingDate])=9,12), tblMembers.Status, tblAttendance.TypeOfMeeting
ORDER BY [LastName] & ", " & [PreferredName], Sort: Switch(Month([MeetingDate])=10,1,Month([MeetingDate])=11,2,Month([MeetingDate])=12,3,Month([MeetingDate])=1,4,Month([MeetingDate])=2,5,Month([MeetingDate])=3,6,Month([MeetingDate])=4,7,Month([MeetingDate])=5,8,Month([MeetingDate])=6,9,Month([MeetingDate])=7,10,Month([MeetingDate])=8,11,Month([MeetingDate])=9,12), Format([MeetingDate],"mmm yyyy") DESC 
PIVOT Format([MeetingDate],"mmm yyyy");

I want to do 2 things...
1. Sort in the order by the Sort column which is based on a Switch function, i.e. October, November, December, January, February...through to September. And I do not want to use Fixed Column Headings, because that won't work in this instance.
2. Show each member across on one line - e.g. Anderson, Oct 08, Nov 08, Dec 08, Jan 09...on through to Sep 09.

Can anyone suggest how to fix this so that happens?

Thanks.

Tom

 

[SkipVought]

Tom,

What IS happening with this Transform?

Your problem is that the date is being conveted to a STRING, and is collating in ALPHA order rather than NUMERIC order as would be with a DATE.

Format([MeetingDate],"mmm yyyy") returns a STRING.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[THWatson]

Thanks, Skip

I see one of the problems. I have the Switch function in twice. So I have removed both of those.

My new SQL is

PARAMETERS [Forms]![frmDateSelector]![txtStartDate] DateTime, [Forms]![frmDateSelector]![txtEndDate] DateTime;
TRANSFORM Count(tblAttendance.Present) AS CountOfPresent
SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblAttendance.TypeOfMeeting, Count(tblAttendance.MeetingDate) AS [Meetings Attended]
FROM tblMembers LEFT JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
WHERE (((tblAttendance.TypeOfMeeting)="Regular Meeting") AND ((tblMembers.Status)<>"Deceased" And (tblMembers.Status)<>"Transferred Out") AND ((tblAttendance.Present)=Yes))
GROUP BY tblMembers.MemberID, [LastName] & ", " & [PreferredName], tblMembers.Status, tblAttendance.TypeOfMeeting
ORDER BY [LastName] & ", " & [PreferredName], Format(CDate([MeetingDate]),"mmm yyyy") DESC 
PIVOT Format(CDate([MeetingDate]),"mmm yyyy");

But this doesn't get me the correct answer.

How do I change the sql and omit the date being collated in ALPHA rather than NUMERIC?

Tom

 

[SkipVought]

I assume that [MeetingDate] is ALREADY a Date, so CDate does absolutely nothing!

PIVOT [MeetingDate];

You must Pivot on the DATE VALUE, which is NUMERIC.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[THWatson]

Okay, this gives me the correct sort order.

PARAMETERS [Forms]![frmDateSelector]![txtStartDate] DateTime, [Forms]![frmDateSelector]![txtEndDate] DateTime;
TRANSFORM Count(tblAttendance.Present) AS CountOfPresent
SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblAttendance.TypeOfMeeting, Count(tblAttendance.MeetingDate) AS [Meetings Attended]
FROM tblMembers LEFT JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
WHERE (((tblAttendance.TypeOfMeeting)="Regular Meeting") AND ((tblMembers.Status)<>"Deceased" And (tblMembers.Status)<>"Transferred Out") AND ((tblAttendance.Present)=Yes))
GROUP BY tblMembers.MemberID, [LastName] & ", " & [PreferredName], tblMembers.Status, tblAttendance.TypeOfMeeting
ORDER BY [LastName] & ", " & [PreferredName], tblAttendance.MeetingDate DESC 
PIVOT tblAttendance.MeetingDate;

But it gives me the dates for all of the meetings, and I want to show only the month and the total of meetings in that month.

Perhaps I have to use the results of this query to extract the months values into another query.

Tom

 

[SkipVought]

PIVOT Format(tblAttendance.MeetingDate,"yyyy mm");

will collate properly.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

what about this ?

PARAMETERS [Forms]![frmDateSelector]![txtStartDate] DateTime, [Forms]![frmDateSelector]![txtEndDate] DateTime;
TRANSFORM Count(A.Present) AS CountOfPresent
SELECT M.MemberID, [LastName] & ', ' & [PreferredName] AS FullName, A.TypeOfMeeting, Count(A.MeetingDate) AS [Meetings Attended]
FROM tblMembers AS M INNER JOIN tblAttendance AS A ON M.MemberID = A.MemberID
WHERE M.MeetingDate BETWEEN [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]
AND A.TypeOfMeeting='Regular Meeting' AND M.Status<>'Deceased' AND M.Status<>'Transferred Out' AND A.Present=Yes
GROUP BY M.MemberID, [LastName] & ', ' & [PreferredName], A.TypeOfMeeting
ORDER BY [LastName] & ", " & [PreferredName] 
PIVOT Format(A.MeetingDate,"yyyy mm mmm");

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Skip
Well, in fact, it doesn't.

If I use that on its own it gives me "You tried to execute a query that does not include the specified expression tblAttendance.MeetingDate as part of an Aggregate function."

So if I add tblAttendance.MeetingDate in as an additional column, the query runs, but I get 394 results whereas I should only get 41.

Arggh! I have learned to hate crosstab queries...as useful as they are I still loathe them.

Tom

 

[SkipVought]

GROUP BY M.MemberID, [LastName] & ', ' & [PreferredName], A.TypeOfMeeting, A.MeetingDate

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[THWatson]

PHV
I got Microsoft Jet does not recognize M as part of an Aggregate function. And then I got Microsoft Jet does not recognize [LastName] & ', ' & [PreferredName] as part of an aggregate function.

One step forward, two steps back.

Tom

 

[PHV]

Microsoft Jet does not recognize M as part of an Aggregate function
With which SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
With your sql code.

Tom

 

[THWatson]

This code is starting on the right track...

PARAMETERS [Forms]![frmDateSelector]![txtStartDate] DateTime, [Forms]![frmDateSelector]![txtEndDate] DateTime;
TRANSFORM Count(A.Present) AS CountOfPresent
SELECT M.MemberID, A.TypeOfMeeting, Count(A.MeetingDate) AS [Meetings Attended]
FROM tblMembers AS M INNER JOIN tblAttendance AS A ON M.MemberID = A.MemberID
WHERE (((A.MeetingDate) Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]) AND ((A.TypeOfMeeting)='Regular Meeting') AND ((M.Status)<>'Deceased' And (M.Status)<>'Transferred Out') AND ((A.Present)=Yes))
GROUP BY M.MemberID, [LastName] & ', ' & [PreferredName], A.TypeOfMeeting
ORDER BY Format(A.[MeetingDate],"yyyy mmm")
PIVOT Format(A.[MeetingDate],"yyyy mmm");

The problem is if I sort the Pivot column Ascending I get
Jan 09, Feb 09, Oct 08, Nov 08, Dec 08

If I sort the Pivot column Descending I get Dec 08, Nov 08, Oct 08, Feb 09, Jan 09

Unsorted gives the same as Descending.

Tom

 

[THWatson]

The reason I want it to sort from October 2008 through March 2009 is that the Club's fiscal year is from October 1 of one year through September 30 0f the next year.

Tom

 

[SkipVought]

Format(A.[MeetingDate],"yyyy mmm");

THINK about it. How will this TEXT sort???
[tt]
2008 Oct
2008 Nov
2008 Dec
2009 Jan
2009 Feb
2009 Mar
2009 Apr
2009 May
2009 Jun
2009 Jul
2009 Aug
2009 Sep
[/tt]
Guess what...
[tt]
2008 Dec
2008 Nov
2008 Oct
2009 Apr
2009 Aug
2009 Feb
2009 Jan
2009 Jul
2009 Jun
2009 Mar
2009 May
2009 Sep
[/tt]
It's [RED]TEXT[/RED]

How about this real date, representing the first of the month for each date...

PIVOT DateSerial(Year(A.[MeetingDate]), Month(A.[MeetingDate]), 1);

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[THWatson]

Skip
Eureka! We've...nope you've...got it!

Sorry. I was going by your post from a few back when you said

PIVOT Format(tblAttendance.MeetingDate,"yyyy mm");

Whew. I was beginning to think it wasn't doable. I hadn't used DateSerial in a sort before.

Now, my next hurdle is to work through how I restrict Makeup Activities to a maximum of 4 in a month. Perfect attendance credit is given when a member has a combination of attending regular meetings and Makeups totalling 4 in a 4 Thursday month and 5 in a 5 Thursday month, with the proviso that Makeups can only number 4 irrespective of the number engaged in.

You have been a terrific help!!

Thanks.

Tom