Crosstab query with irregularly-spaced dates

[Carl Rapson]

I've got a query that returns a set of data consisting of a date field, a
group field, and a numeric field. The data set consists of 4 different
dates, each of which will have an entry for each of 3 different groups (for
a total of 12 records). I want to create a crosstab query based on this
data, using the group values as the row headers, the date values as the
column headers, and the numeric field as the calculated field.
Unfortunately, the date values are not regularly spaced; that is, each date
represents a "labor closing" date which can vary from month to month. For
example, a set of upcoming data will have date values of "5/27/2005",
"7/1/2005", "7/29/2005", and "8/26/2005".

Since I want to go on to use the crosstab query as the RecordSource for a
report, I need to make the dates relative somehow so that the report can
insert the correct values at run-time and I won't need to produce a
brand-new report each time. I have created such a crosstab query when the
dates are evenly spaced, such as for a set of dates that are exactly one
week apart, and was able to also create a report based on that query - the
report handles replacing the relative dates with the correct date values.
But I can't figure out how to handle the irregularly-spaced dates as noted
above. The report will be run from a form, and I have added some (hidden)
text boxes to the form containing the 4 date values, thinking I might be
able to refer back to those text boxes to get the actual dates. But I still
can't figure out how to create the crosstab query with relative dates to
base the report on.

I would appreciate any suggestions as to how to approach this problem. If
more information is needed (code listings, for example) I will be happy to
provide it.

Thanks for any help,

Carl Rapson

 

[[MVP] S.Clark]

When all else fails create a new table, of which the report will be based.
Using action queries, populate the table with the needed data. The report
labels can be modified dynamically to match the data values, if needed, via
VBA.

 

[Carl Rapson]

Thanks, Steve. After thinking about it a while, I came up with something
similar. I added a number field to the table and put a sequence number in
that field when I add the date data. Then, I based my crosstab query on the
sequence number instead of the date, allowing me to create the relative
columns I wanted. Then, as you suggest, I handle the report labels with VBA
code at report runtime. I don't know if this was the best way to handle it,
but it works and so I'm not going to mess with success.

Carl Rapson