Access 2007: Multiple Tables

1. Access 2007: Multiple Tables Health Science Center IT Center – Training training@health.ufl.edu 352-273-5051

2. Name: Barry HarrisCollege: MedicineTel: 392-5555 Basic Database Concepts • Table • A set of related records • Record Name: Barry HarrisCollege: MedicineTel: 392-5555 • A collection of data about an individual item • Field Name: Barry Harris • A single item of data common to all records

3. An Example of a Table Fields Records

4. Review of the Basic Design Rules of Relational Databases • Unique Field Names • Keep fields unique across tables, and keep them as clear as possible in each table. • No Calculated or Derived Fields • Calculations and derivations can be performed in Queries, Forms and Reports. Doing them in a table only increases the chance of data entry error.

5. Review of the Basic Design Rules of Relational Databases • Data is broken down into Smallest Logical Parts • Smallest “Sortable” parts. Remember it’s mucheasier to pull fields together than it is to pull a field apart. • Unique Records • Each of your tables should have unique records. We ensure this by setting one field to be a Primary Key. This can be a user generated field or an AutoNumber.

6. Basic Design Rules We use the unique primary key as our link between our tables, this helps ensure we connect to the correct record. • Unique Records

7. Planning our Patient Table Patient Table (from previous workshop) Pt Med Rec # Pt First Name Pt Last Name Pt Gender Pt Birth Date Pt Phone

8. Planning our Appointment Table Who? What? When? Where? Why? Patient/Doctor Type of visit Date/Time Location Reason What have I got? Patient Name Patient DOB Patient Phone Doctor Name Visit Type Date/Time Location Reason

9. Planning our Appointment Table Does the data belong in this table? Patient Name Patient DOB Patient Phone Doctor Name Visit Type Date/Time Location Reason Pt Med Rec # Pt First Name Pt Last Name Pt Gender Pt Birth Date Pt Phone Pt Med Rec #

10. Planning our Appointment Table Smallest Logical Parts • Pt Med Rec # • Doctor Name • Visit Type • First, Referral, Physical • Date • Time • Location • Shands, MagCtr, Med Plaza • Reason

11. Planning our Appointment Table Unique Field Names • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason

12. Planning our Appointment Table No Calculated or Derived Fields • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason

13. Planning our Appointment Table Primary Key • (AutoNumber) • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason

14. Planning our Appointment Table Multiple Answer Questions MANY OPTIONS Appt Type ONLY ONE Appt Location

15. Planning our Appointment Table Final Plan Appt ID # Pt Med Rec # Appt Doctor Appt Date Appt Time Appt Location Appt Reason Appt Type First Appt Type Referral Appt Type Physical • AutoNumber • Text(from Patients table) • Text • Date/Time • Date/Time • Text (Shands, MagCtr, Med Plaza) • Memo • Yes/No • Yes/No • Yes/No