Microsoft Access Class #2 (Creating a new blank database; relational databases)

1. Lecture: Creating Your Own Database; Planning a Database and a Table

  • Determine the purpose of the database / table
  • Determine the output you need, then the input needed to produce the output
  • Guidelines: 1) Include all of the necessary data      2) Store data in its smallest parts
                          3) Do not use "calculated" fields     (e.g. gpa = quality pts / credits)
  • Set up the data types (Text? Number? Currency? Date? also - Primary Key?)
  • Descriptions clarify the meaning of Field Names. Enter descriptions.
2. Exercise: Create a Database - Patient information
  • Database: Go to the Home Tab, and create a new (Blank) Database, or from the startup screen, use the Office Button to go to New and choose Blank Database.
  • Save the Database under your *OWN* name ( e.g. 12A_Contacts Firstname Lastname) - save in My Documents, or on your flash drive.
  • View: Note that you can either enter the records in Datasheet View, or set up field names and settings first, in Design View.
3. Modify the Fields in the Table (Datasheet View or Design View)
  • Change the field names if needed; one way is double clicking on each - change to more appropriate names than Field1, Field2, etc. Use First Name, Middle, Last Name, Phone, Address, City, State, Zip, Doctor, Amount Owed.
  • To get to Design View, with the table open, go to Datasheet tab, View, and Design View
  • Change the Primary key: Rename it Patient ID. Change the Data type to Text. Note that in Datasheet View, there's a Data Type choice in the Ribbon.
4. Modify the "Properties" of the fields as appropriate
  • FirstName: Field Size to 30 , Required = yes
  • LastName: Field Size to 40 , Required = yes
  • Middle: Field Size to 1 , Format is > (means upper case)
  • State: Field Size to 2 , Format is > (means upper case)
  • Amount Owed: Data type to Currency , Default Value=0
  • Add an "Input Mask" for Phone # and for Zip Code
  • Go to Datasheet View (and Save the Table as "12A Patients FirstName LastName")
5. Add a second table, for Doctors.
  • Go to the Create tab - choose "Table Templates", and choose "Contacts".
  • To remove Columns: Click on the field header and right click to find "Delete"
  • Change the field names:as appropriate; Change field properties as above
  • Save the Table, with the name "12A Doctors FirstName LastName", when requested.
  • Enter data as per the handout given out in class.
6. Explore sorting the data; also explore creating a Form and a Report.