Sorting a crosstab with more than one value 1

[easyit]

Hi,
I´ve use the FAQ faq701-4524 to create a crosstab to have more than one value.

This works really great, but I´m having trouble sorting my columns. Maybe there is someone who knows how to solve this?

My colums are in the format "year weeknumber itemname" (ie "2005 1 Nf", "2005 1 Value") etc.
I need sorting by year & week but it is being sorted as a string, so "2005 9 Value" has a higher ranking than "2005 42 Value" which is wrong ofcourse.

q_rap_rapportage_basis:

SELECT DatePart("ww",[tbl_urenregistratie]![datum],0,0) AS Week, DatePart("yyyy",[tbl_urenregistratie]![datum],0,0) AS Jaar, tbl_activiteiten.activiteit, tbl_teams.Team_Naam, tbl_urenregistratie.aantal_verwerkt AS Aantal, tbl_urenregistratie.tijdsduur AS [Totaal tijdsduur], [Totaal tijdsduur]/[Aantal] AS [Gem tijd], DatePart("q",[tbl_urenregistratie]![datum],0,0) AS Kwartaal, DatePart("m",[tbl_urenregistratie]![datum],0,0) AS Maand
FROM tbl_activiteiten INNER JOIN (tbl_medewerkers INNER JOIN (tbl_teams INNER JOIN tbl_urenregistratie ON tbl_teams.key = tbl_urenregistratie.FK_team) ON tbl_medewerkers.key = tbl_urenregistratie.FK_medewerker) ON tbl_activiteiten.key = tbl_urenregistratie.FK_Activiteit
WHERE (((DatePart("yyyy",[tbl_urenregistratie]![datum],0,0))=Year(Date())))
ORDER BY DatePart("ww",[tbl_urenregistratie]![datum],0,0) DESC , DatePart("yyyy",[tbl_urenregistratie]![datum],0,0) DESC , tbl_activiteiten.activiteit, tbl_teams.Team_Naam;

Thecrostabquery:

TRANSFORM Sum(((IIf([tbl_Kruis_kolomnaam]![KolomkopNaam]="Aantal",[q_rap_rapportage_basis]![Aantal],[q_rap_rapportage_basis]![Gem Tijd])))) AS AanTijd
SELECT q_rap_rapportage_basis.activiteit, q_rap_rapportage_basis.Team_Naam
FROM tbl_Kruis_kolomnaam, q_rap_rapportage_basis
WHERE (((q_rap_rapportage_basis.Jaar)=Year(Date())))
GROUP BY q_rap_rapportage_basis.Jaar, q_rap_rapportage_basis.activiteit, q_rap_rapportage_basis.Team_Naam
ORDER BY q_rap_rapportage_basis.activiteit, q_rap_rapportage_basis.Team_Naam
PIVOT [q_rap_rapportage_basis]![jaar] & " " & [q_rap_rapportage_basis]![week] & " " & [KolomkopNaam];

I've tried formating the weeknumber with a leading zero, but than my columns are not displaye at all.

Any help is appreciated!

 

[PHV]

You may consider this:
PIVOT [q_rap_rapportage_basis]![jaar] & Format([q_rap_rapportage_basis]![week]," 00 ") & [KolomkopNaam];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[easyit]

...I'm speechless. So obvious en simple, thank you very much!