Generating reports

[Bruce]

Thanks to JulieD, Roger Carlson et al, and to several
books I have acquired, I have made great progress with
some databases. Here is the general setup: I need to
record training for employees. Most training is done in-
house, and it can be on any topic. Rather than there
being a course list, training session information is
recorded on the fly. I have an Employees table, which is
relatively static, and a Session table, which changes with
every new training session. The Session table includes
training topic, part number, instructor, etc. Each
employee may attend many training sessions, and each
training session may have many attendees. A junction
table (tblEnrollment) containing an autonumber (just in
case I need a PK some day), FKs to match the PK from each
of the other two tables, and a field for the date (not
necessarily the same for each employee at a training
session).
In terms of storing data, it works as hoped, but I am
having trouble extracting the information I need for
reports. One report is to list all of an employee's
training within a date range. I made a query out of the
Employees, Session, and junction tables as described
above, and set it up as a parameter query to find the
employee and specify the date range. When I base a report
on that query it prompts me for the criteria, as planeed,
but I would like the report to be a continuous listing of
that employee's training, thus:
Johnny Jones
Training Description: Fork Truck Safety
Instructor: Lefty Gauche
Date: 3/9/04
Training Description: CPR
etc.
If I put the employee's name into the page header, and
don't put a page break into the detail, it works fine.
However, sometimes I need to generate reports for all
employees. I have set up the parameter so that leaving
blank the dialog box that prompts for employee name
produces records for all employees. If I do that for the
report, it does not work as I would like, which is to have
the employee's name appear at the top of the page and all
of that employee's training listed below (however many
pages it takes). Instead, an employee's name appears at
the top of each page, but not all employees are listed,
and the training records on the page are not all for the
person listed at the top.
I hesitate to compound this posting with too wide a range
of questions, but I do have another. Sometimes the entry
N/A appears on the Sessions form (based on the Sessions
table) when that field is not relevant to the training
session (for instance, Part Number is not relevant to CPR
training). I would prefer that it not show up on the
report. I know that if I set the property of the text box
to Can Shrink, then if that field is empty the text box
will not appear. I would like the same result if the
field contains the value N/A. Also, in a case where the
text box is empty, I would like the label to disappear.
For instance, if Part Number is N/A or Null, the Part
Number label on the report will not be shown (and will not
take up space).

 

[Chris]

Okay, I think I know what you are talking about...

There are two ways to do the first question. The easiest
is to add an Employee group header. The second is to show
the employee name, and set the HideDuplicates property to
True.

The second is a little more complicated. For the
Detail_OnFormat event, use: (warning, Air Code ahead)

PartNumber.Visible = Not (PartNumber = "N/A" or PartNumber
= "" or IsNull(PArtNumber)


Chris

 

[Bruce]

Thanks, that set me on the right track. I used the group
heading approach. I am still trying to figure out some of
the details, though. In the Sorting and Grouping dialog
box I put Last, then First, then Full Name. Full Name is
a calculated (concatenated) field in the query on which
the report is based. I set group header to Yes for Full
Name only, and dragged the field into the header. I
finally figured out that in header properties I need to
set Force New Page to Before Section in order to keep all
of a person's training records together on a page. I also
set Repeat Section to Yes in order for the group header to
appear at the top of subsequent pages. Back to the
Grouping dialog box, I set Keep Together to With First
Detail for Full Name, and to No for Last and First. My
understanding of With First Detail is that if the group
goes to a second page, it will not split a record. Many
of the records will occupy more than one line, and I don't
want the first line at the bottom of one page and the
second line at the top of the next page. Have I
overlooked or misunderstood anything?
Regarding your second answer, I added a parentheses at the
end, and then it worked as expected (I used it for several
fields), with a twist. I set the Can Shrink property of
the text boxes to Yes. Curiously (to me, at least), the
only fields (text boxes) that would shrink were ones with
labels. Fields from which I had removed the labels still
took up vertical space, although they were blank. I
deleted those fields, then dragged replacements onto the
report, this time with the labels. This time they shrunk
when they were empty.
As it happens (change of plans) I will be arranging the
data into columns, with the fields identified by column
labels in the header rather than by a label each time the
field appears in the detail section. I will be applying
your suggestion for eliminating "N/A" values (which was
easy to implement, not complicated at all), and I wonder
if I need to leave all of the labels with the text boxes.
I can set the labels to Invisible, since they are not
needed, but it seems silly to need to keep them at all.
Or am I hallucinating?