Sorting Crosstab after Format of Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a Crosstab query to work properly. I can data to show but
it is sorting

Apr-04 Apr-05 Aug-04 Dec-04 Dec-05

I realize that when I formatted the date in the test001 query that it
becomes a string, but I left in the original date as my sorting option.
However I can't get it to sort the column heading in true date sort. Help
please.

This is the cross tab query:

TRANSFORM Sum(test001.Hours) AS SumOfHours
SELECT test001.ProjectID, test001.Description, test001.Role,
Sum(test001.Hours) AS [Total Of Hours]
FROM test001
GROUP BY test001.ProjectID, test001.Description, test001.Role,
test001.MyDateSort
ORDER BY test001.MyDateSort
PIVOT test001.MyDate;

This is the Test001 Query:

SELECT Project_Tracking.ProjectID, Project_ID.Description, PERSONEL.Role,
Project_Tracking.Hours, Project_Tracking.recDate AS MyDateSort,
Format([recDate],"mmm") & "-" & Format([recDate],"yy") AS MyDate
FROM PERSONEL INNER JOIN (Project_ID INNER JOIN Project_Tracking ON
Project_ID.ProjectID = Project_Tracking.ProjectID) ON PERSONEL.Initials =
Project_Tracking.Initials
GROUP BY Project_Tracking.ProjectID, Project_ID.Description, PERSONEL.Role,
Project_Tracking.Hours, Project_Tracking.recDate, Format([recDate],"mmm") &
"-" & Format([recDate],"yy")
ORDER BY Project_Tracking.recDate;
 
Ok, that works - but the aesthetics of it don't quite meet what I was trying
to do.

However I can correct that - Thanks for the help.


Duane Hookom said:
Is there a reason you couldn't use:
Format([recDate],"yy-mm") AS MyDate


--
Duane Hookom
MS Access MVP
--

Steven M. Britton said:
I am trying to get a Crosstab query to work properly. I can data to show
but
it is sorting

Apr-04 Apr-05 Aug-04 Dec-04 Dec-05

I realize that when I formatted the date in the test001 query that it
becomes a string, but I left in the original date as my sorting option.
However I can't get it to sort the column heading in true date sort. Help
please.

This is the cross tab query:

TRANSFORM Sum(test001.Hours) AS SumOfHours
SELECT test001.ProjectID, test001.Description, test001.Role,
Sum(test001.Hours) AS [Total Of Hours]
FROM test001
GROUP BY test001.ProjectID, test001.Description, test001.Role,
test001.MyDateSort
ORDER BY test001.MyDateSort
PIVOT test001.MyDate;

This is the Test001 Query:

SELECT Project_Tracking.ProjectID, Project_ID.Description, PERSONEL.Role,
Project_Tracking.Hours, Project_Tracking.recDate AS MyDateSort,
Format([recDate],"mmm") & "-" & Format([recDate],"yy") AS MyDate
FROM PERSONEL INNER JOIN (Project_ID INNER JOIN Project_Tracking ON
Project_ID.ProjectID = Project_Tracking.ProjectID) ON PERSONEL.Initials =
Project_Tracking.Initials
GROUP BY Project_Tracking.ProjectID, Project_ID.Description,
PERSONEL.Role,
Project_Tracking.Hours, Project_Tracking.recDate, Format([recDate],"mmm")
&
"-" & Format([recDate],"yy")
ORDER BY Project_Tracking.recDate;
Click to expand...
Click to expand...
 
Back
Top