Cross Tab with dynamic date column

[Oded Dror]

Hi there,

I'm using access 2003 on XP Pro SP2

I have a cross tab query (for payroll)

One Row Heading for Date Format([Check Date],"WW")
One Column Heading for [Employee Name]
One Sum Column for [Amount]

Very simple and works fine and it give me every week a new column

The problem start when I'm trying to create a report
I want to show the last 8 week (columns) in my report dynamically
And want it done automatically without having to change the column name
every week
(Because the column name change every week in addition to the rest of
columns)

Is there any way to create a generic that it doesn't matter what is the
column name just show 8 column all the time

I can setup the query to show 8 week only. what about the report?

Thanks,
Oded Dror
Email: (e-mail address removed)

 

[Duane Hookom]

You stated the date was the Row Heading but it seems this is actually your
Column Heading. You can change your Column Heading to relative dates with an
expression like:
ColHead: "Wk" & DateDiff("ww",[Check Date],Date())
Then set the Column Headings property to something like:
Column Headings: "Wk0","Wk1","Wk2",...."Wk7"
Wk0 will display values for the current week. Wk7 will be 8 weeks ago.

 

[Oded Dror]

Duane,

Ok I did that but how I can show lets say in week 11 I want to see weeks
(3-11) and week 12 (4-12) and so on
How to make the report to grab the additional column and subtract one column

Is like a windows all you can see is 8 column that moving every week (based
on parameter in the query)
Is there any way to declare a column regardless to it name
All the problems is the column name next week I will get an error "Don't
find column Wk9 or what ever name

Thanks,
Ed Dror

You stated the date was the Row Heading but it seems this is actually your
Column Heading. You can change your Column Heading to relative dates with
an
expression like:
ColHead: "Wk" & DateDiff("ww",[Check Date],Date())
Then set the Column Headings property to something like:
Column Headings: "Wk0","Wk1","Wk2",...."Wk7"
Wk0 will display values for the current week. Wk7 will be 8 weeks ago.

--
Duane Hookom
Microsoft Access MVP

Hi there,

I'm using access 2003 on XP Pro SP2

I have a cross tab query (for payroll)

One Row Heading for Date Format([Check Date],"WW")
One Column Heading for [Employee Name]
One Sum Column for [Amount]

Very simple and works fine and it give me every week a new column

The problem start when I'm trying to create a report
I want to show the last 8 week (columns) in my report dynamically
And want it done automatically without having to change the column name
every week
(Because the column name change every week in addition to the rest of
columns)

Is there any way to create a generic that it doesn't matter what is the
column name just show 8 column all the time

I can setup the query to show 8 week only. what about the report?

Thanks,
Oded Dror
Email: (e-mail address removed)

 

[Duane Hookom]

What do you mean by "I did that"? If you did as I suggested, you would always
have columns named Wk0 - Wk7. Wk0 would be the current week and Wk7 would be
8 weeks ago. Isn't this the 8 week "window" that you need? If you want
something other than ending on the current week, you would need to replace
the Date() function with a reference to a control on a form.

This link http://www.tek-tips.com/faqs.cfm?fid=5466 explains how to create a
dynamic monthly crosstab report. You should be able to modify the date
increment from month to week.

--
Duane Hookom
Microsoft Access MVP

Duane,

Ok I did that but how I can show lets say in week 11 I want to see weeks
(3-11) and week 12 (4-12) and so on
How to make the report to grab the additional column and subtract one column

Is like a windows all you can see is 8 column that moving every week (based
on parameter in the query)
Is there any way to declare a column regardless to it name
All the problems is the column name next week I will get an error "Don't
find column Wk9 or what ever name

Thanks,
Ed Dror

You stated the date was the Row Heading but it seems this is actually your
Column Heading. You can change your Column Heading to relative dates with
an
expression like:
ColHead: "Wk" & DateDiff("ww",[Check Date],Date())
Then set the Column Headings property to something like:
Column Headings: "Wk0","Wk1","Wk2",...."Wk7"
Wk0 will display values for the current week. Wk7 will be 8 weeks ago.

--
Duane Hookom
Microsoft Access MVP

Hi there,

I'm using access 2003 on XP Pro SP2

I have a cross tab query (for payroll)

One Row Heading for Date Format([Check Date],"WW")
One Column Heading for [Employee Name]
One Sum Column for [Amount]

Very simple and works fine and it give me every week a new column

The problem start when I'm trying to create a report
I want to show the last 8 week (columns) in my report dynamically
And want it done automatically without having to change the column name
every week
(Because the column name change every week in addition to the rest of
columns)

Is there any way to create a generic that it doesn't matter what is the
column name just show 8 column all the time

I can setup the query to show 8 week only. what about the report?

Thanks,
Oded Dror
Email: (e-mail address removed)

 

[Oded Dror]

Duane,

It works,

Thanks you very much

Oded Dror

What do you mean by "I did that"? If you did as I suggested, you would
always
have columns named Wk0 - Wk7. Wk0 would be the current week and Wk7 would
be
8 weeks ago. Isn't this the 8 week "window" that you need? If you want
something other than ending on the current week, you would need to replace
the Date() function with a reference to a control on a form.

This link http://www.tek-tips.com/faqs.cfm?fid=5466 explains how to create
a
dynamic monthly crosstab report. You should be able to modify the date
increment from month to week.

--
Duane Hookom
Microsoft Access MVP

Duane,

Ok I did that but how I can show lets say in week 11 I want to see weeks
(3-11) and week 12 (4-12) and so on
How to make the report to grab the additional column and subtract one
column

Is like a windows all you can see is 8 column that moving every week
(based
on parameter in the query)
Is there any way to declare a column regardless to it name
All the problems is the column name next week I will get an error "Don't
find column Wk9 or what ever name

Thanks,
Ed Dror

You stated the date was the Row Heading but it seems this is actually
your
Column Heading. You can change your Column Heading to relative dates
with
an
expression like:
ColHead: "Wk" & DateDiff("ww",[Check Date],Date())
Then set the Column Headings property to something like:
Column Headings: "Wk0","Wk1","Wk2",...."Wk7"
Wk0 will display values for the current week. Wk7 will be 8 weeks ago.

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm using access 2003 on XP Pro SP2

I have a cross tab query (for payroll)

One Row Heading for Date Format([Check Date],"WW")
One Column Heading for [Employee Name]
One Sum Column for [Amount]

Very simple and works fine and it give me every week a new column

The problem start when I'm trying to create a report
I want to show the last 8 week (columns) in my report dynamically
And want it done automatically without having to change the column
name
every week
(Because the column name change every week in addition to the rest of
columns)

Is there any way to create a generic that it doesn't matter what is
the
column name just show 8 column all the time

I can setup the query to show 8 week only. what about the report?

Thanks,
Oded Dror
Email: (e-mail address removed)