Missing Weeks/Months in a Crosstab

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
 
D

Duane Hookom

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",...
 
M

MGFoster

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. ...)
 
Top