Reporting on a crosstab query with date headers

[Rod Makin]

All,

I would appreciate some help on the following - it may be a conceptual
problem in understanding databases for me but am really struggling at the
minute.
I have created a Log of Events for tracking software faults, as part of the
reporting they want to take a snapshot of the condition of the software
every day. I have this query that appends to a table every day. I have then
got a crosstab query to create the following sort of data:-

doc_no phase result 01/11/03 02/11/03 03/11/03 04/11/03
05/11/03
tst1 FAT PASS 5 8 10
12 15
tst1 FAT FAIL 0 1 1
2 1
tst1 FAT DEFER 0 2 2
1 1
tst2 FAT PASS 0 0 2
10 15
tst2 FAT FAIL 5 5 7
3 2
tst2 FAT DEFER 0 2 3
5 1

My problem is that when I try to report on the data the header are being
referenced as the date, so when I use this on a different project next month
the headers will be wrong. If I put the headers as a nuumber of days from
today, eg start at 0 then work down up and calculate the date dynamically,
this works until you have insufficient data to fill all the headers.

How can I resolve this? ANy ideas?

Cheers in advance



Rod

 

[Marshall Barton]

All,

I would appreciate some help on the following - it may be a conceptual
problem in understanding databases for me but am really struggling at the
minute.
I have created a Log of Events for tracking software faults, as part of the
reporting they want to take a snapshot of the condition of the software
every day. I have this query that appends to a table every day. I have then
got a crosstab query to create the following sort of data:-

doc_no phase result 01/11/03 02/11/03 03/11/03 04/11/03
05/11/03
tst1 FAT PASS 5 8 10
12 15
tst1 FAT FAIL 0 1 1
2 1
tst1 FAT DEFER 0 2 2
1 1
tst2 FAT PASS 0 0 2
10 15
tst2 FAT FAIL 5 5 7
3 2
tst2 FAT DEFER 0 2 3
5 1

My problem is that when I try to report on the data the header are being
referenced as the date, so when I use this on a different project next month
the headers will be wrong. If I put the headers as a nuumber of days from
today, eg start at 0 then work down up and calculate the date dynamically,

You're definately on the right track.

this works until you have insufficient data to fill all the headers.

Use the query's ColumnHeadings property to specify the
headings that the report is prepared to deal with. This
will both eliminate columns that are not listed and provide
those column whether there is data or not.

 

[Duane Hookom]

Here is a re-post from a previous thread:
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
It looks like your report will contain 31 days ending today.
ColHead:"Day" & DateDiff("d", [DateField], Date())
Set the Column Headings property to
"Day0","Day1",..."Day30"
Day0 will be today and Day30 will be 30 days earlier. Your report and query
will always have the same columns/fields. In your report, you can use text
boxes as column labels:
=DateAdd("d",0,Date())
=DateAdd("d",-1,Date())
=DateAdd("d",-2,Date())
=DateAdd("d",-3,Date())
....
=DateAdd("d",-30,Date())

 

[Rod Makin]

Duane / Marshall,

Cheers for that I finally got it to work with your help

Regards



Rod

Here is a re-post from a previous thread:
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
It looks like your report will contain 31 days ending today.
ColHead:"Day" & DateDiff("d", [DateField], Date())
Set the Column Headings property to
"Day0","Day1",..."Day30"
Day0 will be today and Day30 will be 30 days earlier. Your report and query
will always have the same columns/fields. In your report, you can use text
boxes as column labels:
=DateAdd("d",0,Date())
=DateAdd("d",-1,Date())
=DateAdd("d",-2,Date())
=DateAdd("d",-3,Date())
...
=DateAdd("d",-30,Date())

--
Duane Hookom
MS Access MVP

All,

I would appreciate some help on the following - it may be a conceptual
problem in understanding databases for me but am really struggling at the
minute.
I have created a Log of Events for tracking software faults, as part of the
reporting they want to take a snapshot of the condition of the software
every day. I have this query that appends to a table every day. I have then
got a crosstab query to create the following sort of data:-

doc_no phase result 01/11/03 02/11/03 03/11/03 04/11/03
05/11/03
tst1 FAT PASS 5 8 10
12 15
tst1 FAT FAIL 0 1 1
2 1
tst1 FAT DEFER 0 2 2
1 1
tst2 FAT PASS 0 0 2
10 15
tst2 FAT FAIL 5 5 7
3 2
tst2 FAT DEFER 0 2 3
5 1

My problem is that when I try to report on the data the header are being
referenced as the date, so when I use this on a different project next month
the headers will be wrong. If I put the headers as a nuumber of days from
today, eg start at 0 then work down up and calculate the date dynamically,
this works until you have insufficient data to fill all the headers.

How can I resolve this? ANy ideas?

Cheers in advance



Rod