You need to create a crosstab query with a fixed number of columns where the
columns are sized to hold your widest data value. The column labels need to
be named like LblDate1, Lbldate2, LblDate3 so that you can put the label
names in a loop. The column textboxes need to be named similar to the column
labels so you can put the column textboxes in the same loop as the column
labels. You need a query that only includes the Date from your table data
and the query must return unique dates. A DCount on this query or a
resordset.recordcount on this query sets the total number of columns you
need. Assuming your crosstab report is designed with eight columns, if
(total number of columns needed) Mod 8 = 0, you need to generate (total
number of columns needed)/ 8 pages. If (total number of columns needed) Mod
8 <> 0, you need to generate ((total number of columns needed)/ 8) + 1
pages.
From here you need a loop from 1 to the total number of pages. The crosstab
query needs to be inside the loop and the crosstab query needs a dynamic
criteria so the correct Dates and Data are on the page at each iteration of
the loop. A recordset needs to be built from the Date query and the
recordset needs to be in an internal loop. The internal loop loops from i =
1 to 8. In the internal loop the column label is assigned with Me("LblDate"
& i) = Rst!Date and the column textbox control source is assigned with
Me!("Data" & i) = Rst!Data. The code for the external and internal loops
needs to be in the Open event of the crosstab report. At the end of the
external loop, you need to print the report and then close the report to get
each page of the report. (Page here is not the typical definition of pages
for the report, but is one open, print and close cycle of your code. For
example, if you determine you need 5 pages, you must open, print and close
your report in the external loop five times. In effect you print the same
report over and over just changing the column labels and column data each
time you print.)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1100 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Brad" <no****@devdex.com> wrote in message
news:0p***************@news.uswest.net...
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
*** Sent via Developersdex http://www.developersdex.com ***
