Dynamic Fields report (Access 2000)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I have a table of data that has Date, Data and Category. I need to show, in
a report, each Categories Data by Date. The Date has to be it's own column
across the top and Category down the left side.

As data is entered, the number of unique dates increases. As a result the
number of columns increases. How do you make a report like this in Access? I
know I can do a crosstab query, but I need to format it etc.

I tried using a basic query that uses just the Category, then adding my
crosstab query to it as a SubDatasheet. Access doesn't like that either.

Thanks so much for you help on this.

Brad

Ex:
tblData
Date Category Data
1-1-06 Dog Food 6.5
1-1-06 Cat Food 7.2
1-1-06 Bird Seed 1.6
1-1-06 Dog Food 6.4
1-1-06 Cat Food 7.3
1-1-06 Bird Seed 1.5
2-1-06 Dog Food 6.3
2-1-06 Cat Food 7.9
2-1-06 Bird Seed 1.7
2-1-06 Dog Food 6.4
2-1-06 Cat Food 8.0
2-1-06 Bird Seed 1.9
3-1-06 Dog Food 6.9

Report:

Dog Food 1-1-06 2-1-06 3-1-06
6.5 6.3 6.9
6.4 6.4
Cat Food
7.2 7.9
7.3 8.0
Bird Seed
1.6 1.7
1.5 1.9
 
I recommend you set up your report/query with relative date headings. Your
column heading expression might be something like
ColHead: "D" & DateDiff("d", [Date], Date())
For a full explaination, search google groups on:
mth0 relative group:*Access.Reports* author:hookom
 
Thanks Duane,

I finally found it. Great advice,

Have a great day,

Brad

Duane Hookom said:
I recommend you set up your report/query with relative date headings. Your
column heading expression might be something like
ColHead: "D" & DateDiff("d", [Date], Date())
For a full explaination, search google groups on:
mth0 relative group:*Access.Reports* author:hookom

--
Duane Hookom
MS Access MVP


Brad said:
Thanks for taking the time to read my question.

I have a table of data that has Date, Data and Category. I need to show,
in
a report, each Categories Data by Date. The Date has to be it's own
column
across the top and Category down the left side.

As data is entered, the number of unique dates increases. As a result the
number of columns increases. How do you make a report like this in Access?
I
know I can do a crosstab query, but I need to format it etc.

I tried using a basic query that uses just the Category, then adding my
crosstab query to it as a SubDatasheet. Access doesn't like that either.

Thanks so much for you help on this.

Brad

Ex:
tblData
Date Category Data
1-1-06 Dog Food 6.5
1-1-06 Cat Food 7.2
1-1-06 Bird Seed 1.6
1-1-06 Dog Food 6.4
1-1-06 Cat Food 7.3
1-1-06 Bird Seed 1.5
2-1-06 Dog Food 6.3
2-1-06 Cat Food 7.9
2-1-06 Bird Seed 1.7
2-1-06 Dog Food 6.4
2-1-06 Cat Food 8.0
2-1-06 Bird Seed 1.9
3-1-06 Dog Food 6.9

Report:

Dog Food 1-1-06 2-1-06 3-1-06
6.5 6.3 6.9
6.4 6.4
Cat Food
7.2 7.9
7.3 8.0
Bird Seed
1.6 1.7
1.5 1.9
Click to expand...
Click to expand...
 
Back
Top