Report building debacle

[bridgeman]

Hello. I am a novice access user that has been tasked with helping to
automate some of our antiquated processes. I am using Access and InfoPath. I
am creating a form in Infopath that gives my crew supervisors of which I have
14 the ability to schedule their crews which vary in size from 2 to 4 by
simply filling out a standardized basic form. Each of the crews is assigned
work by a work order number, which is tied to a location and a set of
instructions to be carried out. Each work order is unique. I have 3 tables
one with the work orders which changes daily due to work being added and
completed. The second table contains the names of the members of the crew and
who is assigned to the crew. The third table is designed to capture the
information contained in the InfoPath form.
The problem is that the requirements of the Report based on the information
is proving difficult to tackle.
Here are the requirements.
The format is landscape and on executive paper for the print job.
The Column heading should represent the dates (mon through sun) dispalyed
in mm/dd/yyyy, and should display the work assigned to each crew for each day
of the week. The infomation needed for the work should just be the work order
number, the location, and the work instructions. The The row heading for each
new row should show the crew number and then in a small box just below, the
members of the crew. I don't know if this info is too vague to be of use or
if more is needed. Again I am a novice with this stuff. Any advice would be
great. In advance I thank you.
Cordially,
Bridgeman

 

[S.Clark]

I call this the 'Dynamic Crosstab Report'.

The challenge is that each week, the column headers change, due to the new
date range.

The trick is to make the crosstab query return data in a very generic
structure.

Crew
Day1
Day2
Day3
....
Day7

Write this data to a temp table, as it will make it easier to build/debug
the report. Base all of the textboxes in the report on the generic field
names. The column headers for the report are where the real dynamic nature
occurs. In the OnOpen of the report, you need to update the caption for the
labels for the days(lblDay1... lblDay7). You can pass the starting date via
the OpenArgs of the OpenReport Method. Then, in the report's OnOpen event,
put something like:

lblDay1.caption = me.openargs
lblDay2.caption = me.openargs + 1
etc

Not intuitive, and it will take a while to setup, but once running, it's
very easy to maintain... which is way more important.