Participation Project #13 - Access Fields & Keys

Fields

Fields is represented as a single column of an Access table. You can have multiple fields (columns) for each row. There are a number of ways you can identify the fields such as what you would name the column of the table or a unique set of values arranged in the table that have the same data type. Fields can be numeric, like age, weight, total sales, or non-numeric, such as name, email, major. It's best to make the information in a field to be as small as possible. This means that you don't want to include multiple pieces of information in a single field. For instance, we want to break apart an address so that there is a field for the street, city, state, and zip code individually, instead of storing all of that information into one field. 

Primary Key

A primary key is a unique identifier for a row in a table. This helps to ensure that are no duplicates. An example of a primary key would be a Social Security Number or a Student ID number. Obviously, a social security number or a student ID number is specific to one individual. No matter where you see that student ID number, you'll know it's related to one specific person. This unique identifier will help organize data across multiple tables. 

Composite Key

A composite key is the combination of two fields when it is not possible to have a primary key. For instance, at the University, we use a combination of a course registration code and the semester to create a composite key for courses. Another example would be a full address such as the combination of a street address, city, state, and zip code. 

Be very careful with using a name as a primary or composite key. You would be surprised how many John Smiths there are in the United States (Google says there's close to 25,000). You only want to create a primary key that will be unique and not duplicated somewhere else. If you can not create a primary or composite key, it is best to instead use an autogenerated number that will act as a primary key. 

Relationships

A relationship is how you connect tables. To connect tables, you want to connect the same data type. Access doesn't know that two tables are related unless you create a relationship between them to tell it. A table can have multiple relationships to multiple tables. For instance, we could have multiple tables. Table 1 could be like a student's general record which would include their major, their residency, and whether they are an active or inactive student. We would have a second table with a students grades. To connect these two tables together, we'd need both tables to have a student's ID number in it. 

Sometimes it's important to create a relationship between two tables including more than one field. An example of this would be if you wanted to know the grades a student earned in a semester and also know what their term GPA is for a specific semester. You would need to connect the tables by both the student ID and semester in which they took the course. 

Note:

1. If you receive an error in Access saying there is a "mismatch", it is because your primary or composite keys do not have the same data types. For instance, you can't connect a student name field to a student ID field. The name is non-numeric and the student ID is numeric. Connecting these two field types would be a "mismatch". Instead, you'd need to connect the Student ID fields in two tables.
2. “When creating relationships involving multiple fields, the order in which the tables were selected in specifying the relationship matters. If you cannot create the relationship at first, try reversing the order of the tables.” This is super important, and you should always try this first if building the relationship doesn’t work.