Report based on a Crosstab Query

[James]

I have a Crosstab Query that I want to base a Report on.

Crosstab Query is as follows:

City Number 22/08/05 23/08/05 .........................

CityA 100 30% 31%............................
CityB 101 27% 24%............................

The City and Number fields are fixed but the date field increases each time
a new record is captured for each day.

How do I get the date field to increase as the records increase? When
building the report I have to add each instance of a date manually?

Or is there a better way of building a report on a Crosstab query?

 

[Duane Hookom]

You haven't suggested how far back you want to report. Assuming 10 days....
Use a relative date for your column headings.
Column Headings expression
ColHead: "D" & DateDiff("d",[DateField], Date())
Then set your column headings property
Column Headings: "D9","D8","D7",..."D0"

D9 with be values from 9 days ago and D0 will be today's values.

 

[James]

Thanks Duane, I need all dates in the past and all data added in the future
to display in the report. My orginal problem is that a report doesn't
automatically add dates as they are added to the Crosstab Query.

How must I adjust your formula for this result?

You haven't suggested how far back you want to report. Assuming 10 days....
Use a relative date for your column headings.
Column Headings expression
ColHead: "D" & DateDiff("d",[DateField], Date())
Then set your column headings property
Column Headings: "D9","D8","D7",..."D0"

D9 with be values from 9 days ago and D0 will be today's values.

--
Duane Hookom
MS Access MVP

I have a Crosstab Query that I want to base a Report on.

Crosstab Query is as follows:

City Number 22/08/05 23/08/05 .........................

CityA 100 30% 31%............................
CityB 101 27% 24%............................

The City and Number fields are fixed but the date field increases each
time
a new record is captured for each day.

How do I get the date field to increase as the records increase? When
building the report I have to add each instance of a date manually?

Or is there a better way of building a report on a Crosstab query?

 

[Duane Hookom]

Tell me first what you plan to do when "all dates in the past" would fill
more than one page across?

--
Duane Hookom
MS Access MVP

Thanks Duane, I need all dates in the past and all data added in the
future
to display in the report. My orginal problem is that a report doesn't
automatically add dates as they are added to the Crosstab Query.

How must I adjust your formula for this result?

You haven't suggested how far back you want to report. Assuming 10
days....
Use a relative date for your column headings.
Column Headings expression
ColHead: "D" & DateDiff("d",[DateField], Date())
Then set your column headings property
Column Headings: "D9","D8","D7",..."D0"

D9 with be values from 9 days ago and D0 will be today's values.

--
Duane Hookom
MS Access MVP

I have a Crosstab Query that I want to base a Report on.

Crosstab Query is as follows:

City Number 22/08/05 23/08/05 .........................

CityA 100 30% 31%............................
CityB 101 27% 24%............................

The City and Number fields are fixed but the date field increases each
time
a new record is captured for each day.

How do I get the date field to increase as the records increase? When
building the report I have to add each instance of a date manually?

Or is there a better way of building a report on a Crosstab query?

 

[James]

I have never done it before, but I am assuming that as the dates fill the
report it will flow onto page 2 and then 3 etc.

Is this what you were asking?

 

[Duane Hookom]

I don't leave this to chance. Decide how many columns you want in your
report and stick with it. You may want to allow the user to enter the
"Ending Date" of the columns.