Microsoft Access 4F: Appointment / Invoicing Database

School and Movie Databases:

• The school and movie databases have utilized customer information which is common in many databases

• The school and movie databases have also used transactions (either books or movies)

• The movie database utilized billing reports (or invoicing), also very common

• Another common type of database is one that is used to schedule appointments:
  • You schedule appointments in a seperate table that allows you to select a customer number from a drop list and then assign a date and time.

• In the case of a dentist or doctor office there may be several dentists or doctors to choose from as well.
  • A central receptionist may book all appointments for all the doctors or dentists in the office

• Before an appointment can be booked the receptionist must look up the appointments already made for a particular day to see if there is an opening
  • this is where a query can be used with a parameter where the user enters a date and all appointments for that day appear

• Appointments made in a table or form will need to be sorted from the first to the last(eg. 8:30am to 4:00pm)
  • this is where a query can sort a field in ascending order
  • Note if you were to use a drop list for appointment times eg. 8:30am, 9:00am etc. and you made the format "text" you will have problems!
  • Why?
    • If you try sorting 11:30,12:30,1:30 in ascending order it will appear as 1:30, 11:30 and then 12:30 because text format looks at the first number and 1 is less than 11 and 12
  • Solution
    • Avoid using text format for times, instead use the date/time format
• Another major function of an Appointment/Invoicing database is tracking the fees charged for various procedures in each appointment.

  For a counseling appointment there may be a standard visit fee of $50 or $75 per session

  For a dentist appointment there may be several procedures in each appointmen (cleaning, filling, floride treatment etc. each with a different cost)

  If you were working on an automotive repair garage database, you would use parts and labour instead of procedures.

  One way of tracking fees is to use a seperate table which allows the user to select an AppointmentNumber (eg. 1 as shown below) to a procedure (drop list)

  Note in this one appointment there were two procedures performed (and not paid for yet)

What about the rest of the information (who is the customer, the date of the appointment, the particular dentist etc.)?

You can use a query to get information from the appointment and customer tables.

By using various combinations of queries you can create various reports:

Example reports:

1. Report listing all customers with addresses and telephone numbers in ascending order
2. Report listing all the appointments for a particular day for a particular dentist
3. Report listing all the appointments for a particular time (eg. 11:00 am) to see what dates are not already booked at this time
4. Report listing all the procedures and appointments for a particular customer
5. Report listing all the procedures that have not been paid for by a particular customer (with a total and taxes added in)
6. Report listing all the procedures that have not been paid for by all customers of a particular dentist
7. Report listing all the procedures done by a particular dentist on a particular day

Data Protection:

In this particular case however an employee could collect money from a client, steal the money and then delete the procedure from the table.

Using an autonumber for the "tracking number" is one way of making sure that data is not lost or changed

Example if a procedure was accidentally deleted then this would show up when a report of tblInvoice was made which sorted TrackingNumber in ascending order. You can do an audit at the end of the month to verify that there are no missing numbers.

An employee could still however change a procedure from a more expensive to a cheaper one and keep the difference.

Fraud and Error

Database designers must think of how to safeguard from errors, mistakes and employee fraud. A good database design makes it difficult for this to happen. That is why a business may choose to use a database that costs $50,000 to create versus another that doesn't have the same built in protection but only costs $1,000 to have made.

Example

Large databases for Universities, Hospitals etc. may have millions in development costs and be programmed in programs other than Microsoft Access, however all the same basic principles still apply.

This website is copyright protected see contact page for details.