report with fixed labels/times slots

[wdnalon]

I have a mdb table that has a "review due" date for each individual. I
have a query that returns the individuals that have a "review due"
within a defined time frame (7, 14, 30 days) of a meeting date. Based
on the results of that query, I would like to be able to print a report
that has a table/grid with fixed appointment times (e.g., 9:00a, 9:05a.
9:10a........11:00a) running down the left margin and an individual
listed next to "9:00a", the next individual listed next to "9:05a",
etc.


9:00a record 1
9:05a record 2
..
..
..
11:00a record 24

The appointment times on the report are static and have no pre-defined
relationship to the individuals returned in the query.

If anyone can help with this, I'd appreciate any tips or suggestions.

Thanks in advance!!

 

[Allen Browne]

Okay, so you want to classify your appointments in 5 minute intervals.

Since you want to show the timeslots whether there are appointments or not,
you need another table to generate the 5 minute intervals.

The example below assumes your table has a Date/Time field named AppointTime
which contains both the date and the time of the appointment.

1. Create a table with just one field named Minutes. It is a Number type
field, and primary key. Save the table with the name tblMinute. The table
must contain a record for the number of minutes past midnight you want to
show on your report, from the starting time of the day to the ending time.
For example, if you want 9am - 5pm, enter records:
540
545
550
555
560
...
1020

2. Create a query that partitions your appointment times in 5 minute
intervals. Type this into the Field row in query design:
SlotMinute: 5 * CLng(DateDiff("n", DateValue([AppointTime]),
[AppointTime]) / 5)
The expression gets the number of minutes between the start of the day and
the appointment time, divides by 5 and rounds, the nearest timeslot, and
multiples by 5 again.

3. Add the other fields you want to the grid in the query. Save with the
name "qryAppointSlot". Close.

4. Create another query that uses tblMinutes and qryAppointSlot as source
"tables". Drag tblMinute.Minutes onto qryAppointSlot.SlotMinute so Access
joins them. Double-click the join line. Access pops up a dialog offering 3
options. Choose the one that says:
All records from tblMinute, and all matches from qryAppointSlot.
Save. Close.

5. Create a report based on this last query as its source. In report design
view, open the Sorting And Grouping box (View menu). In the first row of
this dialog, choose the AppointTime field, and in the lower pane set these
properties:
Group Header: Yes
Group On: Day
Access will add a group header section to your report, where you can show
the date of the appointments.

6. On the second row of the Sortin'n'Groupin' dialog, enter the AppointTime
field again. No properties to set: this just ensures the appointments are
sorted in time order. Close the dialog.

7. Add a text box to the Detail section to show the timeslot. Set these
properties:
Control Source: = DateAdd("n", [SlotMinute], 0)
Format: Short Time

8. Add the other appointment fields you wish beside this text box.

How it works
=========
The tblMinute table has a record for every 5-minute period between 9am and
5pm. The outer join between this table and your appointment table (step 4)
generates a record for every timeslot of the day, for every day that
actually has an appointment.

The report has a group header for the date, and matches the appointments by
timeslot.

Note that this report shows only the appointments that fit in its range of
times. If you created an appointment for 8:50am or 5:05pm, it would not show
in this report.

 

[wdnalon]

Hello Allen,

First of all, thank you for the taking the time to provide the code &
explanation. This will be very useful for another task that I have but
as you point out, this strategy assumes that I already have the
appointment date & time assigned.

For the purpose of the review meeting, the appointment dates & times
are not created beforehand; I only have a date for which the review is
due, which may or may not be the actual date of the review meeting. I
would like to have the individuals returned from the "review due" query
plugged into a report that has a blank grid that has static appointment
times. For example, instead of having line numbers for each of the
records, the first record would be labeled "9:00a", the second record
"9:05a", etc. For my current purposes, the labels do not have to be
linked to the individual's data.

Is this possible?

Thank you again in advance for any tips or advice!

Noland

 

[Duane Hookom]

I would at least create a temporary table of schedule records and then use a
calendar report like those found in the samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

 

[wdnalon]

Thanks Duane, I will give that a try.

Noland

 

[wdnalon]

Hello Allen,

I've created the tables and queries as you have instructed but when I
try to run the query created from Step 4, I get an error message
"Data type mismatch in criteria expression". Any ideas?

Thanks!

Noland

 

[Allen Browne]

Type mismatch means that Access is trying to match things that are not the
same data type, e.g. text and number.


At step 1, you created a field wiht a *Number* field (not Date or Text)?

Test the query you created in step 2. Does it work? Do the results
left-align like text, or right-align like numbers?

If it still fails at step 4, switch the query to SQL View (View menu, in
query design), copy the SQL statement, and post it here.