Creating a Simple Database with Microsoft Access 2007

1. Creating a Simple Database with Microsoft Access 2007 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. Basic Design Rules Putting all of the home address in one field may make for convenient data entry, but it makes it very difficult to work with the data. For example, what if I needed to sort by City or Zip Code? Pulling fields together is fairly simple, pulling them apart is very difficult. • Data is broken down into Smallest Logical Parts You can join fields together in queries, forms and reports.

5. Basic Design Rules You also want to be aware of the field names across tables. For example several tables may use the Field LastName. When you use those fields in other parts of the database things can become very confusing very quickly. • Unique Field Names When these two Last Name fields are pulled into the same Query/Form/Report they will appear with the table name in front of the field name: Patient Table.Last Name

6. Basic Design Rules Calculations - If we give an employee a raise, we will need to change both the Hourly Rate and the Standard Pay, and we need to make sure our calculations are correct. Derivations - Since we have the Dept ID, there is no need to include the Dept Name, it can be pulled from the Dept Table. Listing it in both places leads to data entry errors. • No Calculated or Derived Fields Access will let you create calculations in queries, forms and reports.

7. Basic Design Rules If you don’t have unique records, your database can’t tell which record you may be referring to. • Unique Records

8. Primary Keys To ensure that each record is unique in each table, we can set one field to be a Primary Key field. A Primary Key is a field that that will contain no duplicates and no blank values. Looking at the table above, what would be the best Primary Key?

9. Primary Keys While each column in this particular data set has unique data, the field that will work best for us is GL ID (GatorLink). Many employees will work for the same college, have the same last name and possibly even share telephone numbers, but each employee should have a unique GatorLink ID. When there is not a unique field in your data set, you can use an AutoNumber. Access can create incremented or random AutoNumbers for your primary key.

10. 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

11. Planning our Patient Table Name Address Phone Age Medical Record # Emergency Contact Height Weight Date of Last Visit DOB Medications SSN Insurance Allergies Gender Age at First Visit … What have I got?

12. Planning our Patient Table Name Address Phone Age Medical Record # Emergency Contact Height Weight Date of Last Visit DOB Medications SSN Insurance Allergies Gender Age at First Visit Does the data belong in this table?

13. Planning our Patient Table NameFirst/Last Address/City/ST/Zip Phone Age Medical Record # Date of Last Visit DOB SSN Gender Age at First Visit Smallest Logical Parts

14. Planning our Patient Table Pt First Name Pt Last Name Pt Address Pt City Pt STState Pt Zip Pt Phone Pt Age Pt Medical Record # Pt Date of Last Visit Pt DOBBirth Date Pt SSNSocial Sec # Pt Gender Pt Age at First Visit Unique Field Names

15. Planning our Patient Table Pt First Name Pt Last Name Pt Address Pt City Pt State Pt Zip Pt Phone Pt Age Pt Medical Record # Pt Date of Last Visit Pt Birth Date Pt Social Sec # Pt Gender Pt Age at First Visit No Calculated or Derived Fields

16. Planning our Patient Table Pt First Name Pt Last Name Pt Address Pt City Pt State Pt Zip Pt Phone Pt Medical Record # Pt Birth Date Pt Social Sec # Pt Gender (AutoNumber) Primary Key

17. Planning our Patient Table Final Plan Pt Med Rec # Pt First Name Pt Last Name Pt Gender Pt Birth Date Pt Phone

18. Let’s Build our Database!