By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

Specifying date headings in Crosstab Query

P: 10
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  2. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  3. FROM Appointment
  4. WHERE (((Format([Appointment Date],"Short Date")) Between Date() And (Date()+7)))
  5. GROUP BY Appointment.[Consultant ID]
  6. PIVOT Format([Appointment Date],"Short Date");
  7.  
Jan 12 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  2. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  3. FROM Appointment
  4. WHERE (((Format([Appointment Date],"Short Date")) Between Date() And (Date()+7)))
  5. GROUP BY Appointment.[Consultant ID]
  6. PIVOT Format([Appointment Date],"Short Date");
  7.  

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.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  2. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  3. FROM Appointment
  4. WHERE (((Format([Appointment Date],"Short Date")) BETWEEN Date() AND (Date()+7)))
  5. GROUP BY Appointment.[Consultant ID]
  6. PIVOT DateDiff("d",[Date(), [Appointment Date]) & "   " & Format([Appointment Date],"Short Date");
Jan 12 '08 #2

P: 10
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
Jan 13 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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];
Jan 13 '08 #4

P: 10
Heh, to think something so simple was the answer. It worked perfectly puppy, thanks once again :)
Jan 13 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.