Crosstab Query Question

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

MS Access 2000

Here is the pivot column in a crosstab query: PIVOT
DatePart("m",[InvoiceDate])

The possible Invoice Dates in the data are 1/15/2003,
2/20/2003, and 5/17/2003.

The crosstab query produces one column for each month
represented in the data; 1, 2, and 5.


How could I make the query produce each month, 1 - 12,
even if there is no data in every month?

Thanks.
 
PIVOT DatePart("m",[InvoiceDate])In
("1","2","3","4","5","6","7","8","9","10","11","12");

That should do the trick. If you want it for non-finance
peeps:

PIVOT Format([InvoiceDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct
","Nov","Dec");

Someone please help with my query... pleease.
 
Thanks Ken Getz, Paul Litwin and Mike Gilbert. Here's
how...

PIVOT DatePart("m",[InvoiceDate]) In
(1,2,3,4,5,6,7,8,9,10,11,12)


Thanks.
 
Oh yeah, forgot they were numbers not text.

Good luck... still no word on my problem - seems like
using "Last" is not reliable, even if correctly sorted
data.
 
Back
Top