You should be able to do this entirely in the report by sing a multi-column
report with 'across then down' column layout. Create a normal query which
returns the necessary columns and base the report on this. Put all the bound
controls in the far left of the detail section in a single column.
In report design view, in the page setup dialogue set the number of columns
to 20 (or whatever), the column spacing and column width as appropriate,
uncheck the 'same as detail' check box and select 'across then down' as the
column layout.
One further consideration is whether all dates will be represented in the
table, and if not, do you want any missing days to show just the date, with
the other controls below empty? If so you'd need to create an auxiliary
Calendar table. This is just a table of all dates over a period, 10 years
say. A simple way to create one is to serially fill down a column in Excel
and import it into Access as a table. You'd then base the report on a query
which joins the tables in an outer join and returns this date column from the
Calendar table in place of the date column from your current table. You can
include parameters to restrict the dates returned to those in a range entered
when prompted at runtime, so the query would be along these lines:
PARAMETERS [Start date:] DATETIME, [End date:] DATETIME;
SELECT [caldate], [weight], [size], [stretch], [bulk], [grade]
FROM [Calendar] LEFT JOIN [YourTable]
ON [Calendar].[caldate] = [YourTable].[yourDateField]
WHERE [calDate] BETWEEN [Start date:] AND [End date:]
Note that date parameters should always be declared to avoid the risk of a
parameter value entered in short date format being misinterpreted as an
arithmetical expression and giving the wrong results.
The advantage of this approach over a crosstab is that the date 'column
headings' are values in a column returned by the query, so there is no need
to dynamically set the ControlSource properties in the report at runtime to
the date values used as the column headings by a crosstab query.
If there is other data you want returned only once per set of dates then the
multi-column report can be inserted as a sub-report in a parent report. You
can find an example of this at:
http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271
This differs from yours in that there is only one row per column, wrapping
to a second row when the last column is reached, but the principle is much
the same. The file was originally produced in response to a readers request
in a magazine column by a contact of mine, and also contains a single report
solution in which the layout is amended in code at runtime, but that was only
included to show that its possible, not suggested as a serious solution, for
which a multi-column subreport is far simpler.
Ken Sheridan
Stafford, England
