Missing Weeks/Months in a Crosstab

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

I thought I saw something on this recently, but I couldn't find the post and its responses.

I have a crosstab query where the columns are week-end dates, but in my data, there is 3 weeks missing. So my results go from
3-31-2006 in one column to 4-28-2006 in the next column.

Can I get Access to return empty columns for the 3 missing weeks so there isn't a jump in 3 weeks from one column to the next?

Thanks for any help anyone can provide,

Conan Kelly
 
I would use relative date column headings that calculate to "Wk0","Wk1",
"Wk2",... this can be done with DateDiff("ww", somedate, anotherdate). You
can then set your Column headings property to "Wk0","Wk1", "Wk2",...
 
Conan said:
Hello all,

I thought I saw something on this recently, but I couldn't find the post and its responses.

I have a crosstab query where the columns are week-end dates, but in my data, there is 3 weeks missing. So my results go from
3-31-2006 in one column to 4-28-2006 in the next column.

Can I get Access to return empty columns for the 3 missing weeks so there isn't a jump in 3 weeks from one column to the next?
Click to expand...

Use the PIVOT clause:

PIVOT week_end_date IN (#3/31/2006#, #4/7/2006#, #4/14/2006#,
#4/21/2006#, ... etc. ...)
 
Back
Top