creating a report

  • Thread starter Thread starter franky
  • Start date Start date
F

franky

Hello,

I have a report that prints client appointment times
throughout the day (client name, appointment time). I
need it to print starting at the first appointment all the
way to the last appointment time in 15 minute increments
even if no client is schduled at that time.

For example. I have three clients, it needs to look like
this:

Client1 8:15
8:30
Client2 8:45
9:00
Client3 9:15

Any suggestions
 
Start with a correctly structured database.

Have one table called TblDates. It will have DateID BookDate

Have another table called TblTimeIn with TimeInID and TimeIn. (the ID fields
are autonumbers)

In this latter field you type all the appointments that you want during any
day ie
08:00
08:15
etc.

You will have TblCustomer with CustID and then the Customer's details

Now you need TblBooking which has

BookingID, CustID, DateID, TimeInID and any other fields that refer only to
that booking.

Don't enforce referential integrity for the customer table and join TblDate
to TblBooking with a join that includes all the records from TblDate

Have a main form based on TblDate. (You can add all the dates for 1 year to
the tables with code which I can write if you want).
The sub form is based on TblBooking with a combo box to let you put in the
Customer.

Add a command button to the main form and have this code to enter all the
TimeIns to that day.

Private Sub cmbAddTimeSlots_Click()
On Error GoTo Err_cmbAddTimeSlots_Click
Dim MySql As String

MySql = "INSERT INTO TblBooking ( TIMEINID, DATEID )"
MySql = MySql & " SELECT TblTimeIn.TimeInID," & Me.DateID
MySql = MySql & " FROM TblTimeIn;"
DoCmd.SetWarnings False
DoCmd.RunSQL MySql
DoCmd.SetWarnings True

Exit_cmbAddTimeSlots_Click:
Exit Sub

Err_cmbAddTimeSlots_Click:
MsgBox Err.Description
Resume Exit_cmbAddTimeSlots_Click

End Sub


Clients can be added to the form next to the appropriate time slot.

I used a Calendar control to let me turn to the correct page.

Now for the report.

Make a query, QryBooking which has all the fields from TblBooking. From
TblDate add BookDate. From TblTimeIn add TimeIn. (note that TblCustomer
isn't added yet)

Now create a second query. Add to it your query, QryBooking and TblCustomer

Join TblCustomer to QryBooking by CustID.

Double click the join line. Choose the option that 'Includes All the fields
from QryBooking'.

Base your report on this query.

Evi



Add to it, the query
 
Back
Top