Missing Weeks/Months in a Crosstab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Conan Kelly - 06 Jul 2006 19:50 GMT

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

Reply to this Message

Duane Hookom - 06 Jul 2006 20:13 GMT

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

Signature

Duane Hookom
MS Access MVP

> Hello all,
>
[quoted text clipped - 11 lines]
>
> Conan Kelly

Reply to this Message

MGFoster - 06 Jul 2006 21:33 GMT

> Hello all,
>
[quoted text clipped - 4 lines]
>
> 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?

Use the PIVOT clause:

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

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message