First, I should start by saying I am creating a database to be used by
some very impatient, non-computer literate people. It needs to be
seameless in it's operation from their point of view. I want them to
do as little as possible when they run their reports.
I have a crosstab query that displays usage of items for each month.
It looks pretty much like this:
ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC.
1 Solution CS 9 5 1 3
10 Bandaids BX 50 75 25 30
This report is generated by selecting a begin date for the data
collection, in this case 12/1/02. This means that the date columns
WILL change. As the months progress they will not stay the same.
I can get the system to create a rather ugly report based on my
crosstab query, using the Tabular Report Wizard. But I don't like the
way it formats the column headings, so I want to create a report that
resides on the system and doesn't need to be created from the wizard
everytime (thus allowing me to format the page in a more pleasing
manner). Right now the margins are an inch, all the columns are too
skinny, etc.
Of course, the problem with having a report that resides in the
database and is populated by the query is that the column headings
will not change on the report, and once this month passes it will not
pull correctly...
A second issue is the table that the query pulls from has the dates
formated like this: m/yy (which is 1/03 or 12/02 etc.) But when the
crosstab query is generated it displays the dates in the column
headings as "1/1/2003". I've tried putting a formula in that displays
the date as "1/2003" but then it sees it as text and sorts the columns
wrong...if the dates were shorter, maybe letting the wizard create the
report would not be so bad...
So, how can I create a useable report for my crosstab query and/or how
can I format the dates in my crosstab query so they will still sort as
dates, not as text.
Thanks for your help everyone!