Specifying date headings in Crosstab Query

[odavison]

I'm currently running a crosstab query that displays the total amounts of Appointments that each Consultant.

Currently it is Consultant ID as the Rows, and Appointment Date as the top headings, grouped by Date (rather than month etc.)

But it is giving me a random order of dates going up numerically by day and no more.

Is there any way of giving an order to the Date columns or specifying that is should be for the next 7 days etc? here is the SQL Script

TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
FROM Appointment
WHERE (((Format([Appointment Date],"Short Date")) Between Date() And (Date()+7)))
GROUP BY Appointment.[Consultant ID]
PIVOT Format([Appointment Date],"Short Date");
 

[puppydogbuddy]

I'm currently running a crosstab query that displays the total amounts of Appointments that each Consultant.

Currently it is Consultant ID as the Rows, and Appointment Date as the top headings, grouped by Date (rather than month etc.)

But it is giving me a random order of dates going up numerically by day and no more.

Is there any way of giving an order to the Date columns or specifying that is should be for the next 7 days etc? here is the SQL Script

TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
FROM Appointment
WHERE (((Format([Appointment Date],"Short Date")) Between Date() And (Date()+7)))
GROUP BY Appointment.[Consultant ID]
PIVOT Format([Appointment Date],"Short Date");
 

I don't know if this will work, but it is worth a try. I'm using DateDiff to sequence the Pivot Columns. To display the sequence and the date on different lines, increase the number of spaces between the sequencer and the date in the Pivot column until the desired effect is achieved.

TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
FROM Appointment
WHERE (((Format([Appointment Date],"Short Date")) BETWEEN Date() AND (Date()+7)))
GROUP BY Appointment.[Consultant ID]
PIVOT DateDiff("d",[Date(), [Appointment Date]) & "   " & Format([Appointment Date],"Short Date");

[odavison]

I appreciate the help puppy, and I gave your coding a shot, unfortunately it's returning similiar values, and additionally the difference is displaying as a number in the column headings. Aka " -333 17/7/2007". I've tried my hand at rearranging a few things here and there but still getting the same results.

Any more suggestions that I could try out? any feedback is greatly appreciated

[puppydogbuddy]

I appreciate the help puppy, and I gave your coding a shot, unfortunately it's returning similiar values, and additionally the difference is displaying as a number in the column headings. Aka " -333 17/7/2007". I've tried my hand at rearranging a few things here and there but still getting the same results.

Any more suggestions that I could try out? any feedback is greatly appreciated

I just realized that the Format function outputs text strings and that is why your date is not ordering properly. All you need to do is pivot on the appointment date (unformatted as shown below) and it should be ordered propely.

PIVOT [Appointment Date];

[odavison]

Heh, to think something so simple was the answer. It worked perfectly puppy, thanks once again :)