Summary and Detail Report

[MEG]

I'd like some guidance on how best to create an appointment report. The goal
is to have a summary page having one line per appointment and then a one page
report for each appointment.

What is the best way to do this (report/subreport, macro calling two
different reports, etc)? I'd like for the user to only enter the criteria
once and be able to generate the total report.

Thanks,

MEG

 

[Evi]

Do both reports, the summary and the detailed one have to be filtered in
some way?
If they both have to be filtered (eg the appointments for one person,
summarised in the header and detailed below) then you would create the
detailed report and using the Sorting/Grouping box to create a Header for
your PersonID field, put your subreport in that, linking the two reports by
PersonID. In Properties for that Section Header you can set the Force New
Page to be After Section.
If the summary report is not filtered (eg the user wants to see everyone's
appointments), then put it in the Report Header and set the Header to Force
New Page After Section.

Evi

 

[MEG]

Thank you for the response.

It does need to be filtered. The user chooses a reporting date and also can
choose one or ALL workers.

Given that, should a query be created to make the report? Would the
approach be the same?

If so, I don't understand how the data is available for the summary in the
header and then for the detail pages.

Thanks,

MEG

 

[Evi]

Do you want to have a report which is grouped by Appointment Date
then by Worker
then by Appointment Slot (if the Date is thus subdivided) so that you would
see (if unfiltered)

On the first page:
1. The Appointment Date
2.The worker's details
3. A summary of all the appointments *for that worker* and *on that day* in
a table.

On the following pages:
4. A set of 1-appointment-per-page records for that worker on that date

This will be followed by sections 1 to 4 for the next worker until all the
workers are listed then it would start again with an Appointment Date page
for the next date


You can easily put the same table details into one report by creating 2
queries based on the same table. One could be a totals query if you wished
although this is not necessary as you can create your totals when you Group
the report in the Sorting/Grouping box.

Create a main report from one query and, in design view, drag the other
query into the required section of main report. This will then create a
subreport which you can link as required either using the wizard (which
sadly often cuts off the important bit of the info about which fields you
want to group on) or click on your new subreport in Design view and use its
Properties box on the Data Page choosing the field name (or names) you wish
link by by clicking next to where it says Link child Field.

Evi

 

[MEG]

Evi:

Thank you for your response. I followed your answer, but I'm confused on
how what sections you insert the differnt reports. Here is some more
information:

My query is one table. I want to see this type of summary:

John Doe 3/10/2008

Time Location
10:00 AM South County Road
11:30 AM West Rock Road
1:00 PM Flat Rock Lane


Then one page for each appointment time with the details of that appointment.

Which report should be the main and which should be the subreport? And what
sections does it get inserted?

Thanks,

MEG

 

[Evi]

I'm a bit concerned when you say that your query is one table. I hope that
you mean one table which is linked to others (growing gloomy)

The way I've done this is to have
a TimeSlot table which contains a primary key, a sequential number key
(which isn't an autonumber and all the 1/2 hourly time
slots potentially available on any one day with SlotStart as eg 09:00 and
SlotEnd as 09:30

A Date table with the Fields
DateID
and WDate (not just Date of course)
where I add the dates for which i want to make a report (You could add all
the dates for the year if you wish using code or certainly for a month at a
time)

I also have a Worker table with WorkerID and eg WorkerSurname
And finally a Worker TimeSlot table.
which has
WorkTimeID (Primary Key)
DateID
TimeSlotID
WorkerID
AppointDetails (the text field with the details)

This table has an Index in its Design so that DateID+TimeSlotID+WorkerID is
unique. This prevents double bookings so that when a new worker joins, I can
run the query again and the time slots won't be added again the workers who
are already there.

All the Workers, Dates, Timeslots are added to my WorkerTimeSlot table by
simply having an Append query. In this, The Worker table, Date Table and
Time slot table are all unlinked so that every time slot, every day and
every worker are listed. The Unique Index ensures that no combination is
listed twice.

Base your main Report on a query based on your appointments table, Add the
Worker, and Date fields to it.
Group it first by Date then DateID , giving this latter field a footer in
the Sorting/Grouping box. You get set the Force New page Property to After
Section
Then group by Worker and WorkerID, giving this section a header and footer.
Again Force New Page after the footer. Put the Date and Worker Name details
in this Header.
Now group by Time Slot Number giving it a header where you will put the Time
Slot and AppointDetails and a footer, choosing Force New page After section.
This will give you your 1 page per appointment.

There will be nothing in the Details section.

For your subreport, copy and paste the query you used for your main report
but have the Criteria Is Not Null under your Appointment Details field to
filter out the blank appointments.

It needs the WorkerID field and the DateID field, the SlotId, Timeslot and
Appointment details
Your subform can sorted in the Sorting Grouping box by TimeSlot SortNumber
Make the id fields invisible so that only the TimeSlot and AppointDetails
show.

In Design View, slide the subreport into the WorkerID header in your Main
Report
In Properties, on the Data Tab, link this to your main report by *both*
Worker ID and Day ID using the Link Master Key and Link Child Key

Please ask if any of this is not clear.
Evi