Microsoft Access Help

Microsoft^® Access – Creating Tables with Relationship

  §   LOOK UP LISTS

 You can put values from one table into another with a Lookup list. This creates a relationship. 

For example, if you were a farmer, you might have one table for the crops you grow and another table for the fields where they can be planted. This way in your Fields table you can create a lookup list and simply click on the correct Crop from a menu instead of typing it from the keyboard.   

§   ONE-TO-MANY DATABASE RELATIONSHIP

Relationships search for and share data across multiple tables. 

This time, suppose you are an artist and want to keep track of your artwork distributed to galleries. You could create one table to list details about individual pieces and a second table to store records of the galleries that display your work. By creating a relationship between the two tables, you could run a query that would show where each of your items is currently being displayed.  

§   DATABASE DESIGN INCORPORATING RELATIONSHIPS

As a school teacher, I need to track each student's name and personal information, along with the subjects they have taken and their final grade. In my first attempt my columns in one table are:

Name      Home Phone      Address      Subject      Grade 

I enter the student's name and address repeatedly for every new subject. It’s time consuming and when a student changes address and I have to locate and update all the previous entries.

So, I try a different structure with only one record for each student. This requires additional fields:  

Name   Home Phone  Address  Subject1 Grade1  Subject2  Grade2  Subject3 Grade 3

But how many subjects should I allow for? How much space will this waste?Whenever you see a repetition of fields, the data needs to be broken down into separate tables.The solution involves making three tables: one for students, one for subjects, and one for grades.  

§         Student Table:  StudentID, LastName, FirstName, Address, Phone, etc.

§         Subjects Table: SubjectID, Subject and Notes (a brief description of what this subject covers)

§         Grades Table: Two look up lists and one typed entry:

      StudentID - Lookup that ties this entry to a student in the Students table (relationship)

      SubjectID - Lookup that ties this entry to a subject in the Subjects table (relationship)

      Grade - Typed entry  

In creating my database, I enter all the students in the Students table then all the subjects into the Subjects table. There is no duplication of data.  At the end of term when the marks are ready, I enter them in the Grades table using the appropriate lookup list from the Students table and the Subjects table.  I am now able to receive a variety of reports in variety of different layouts.