Database Software 2: Database Objects and Querying a Database
Database Software 2: Database Objects and Querying a Database
Querying a Database
Jennifer Lavergne
Learning Objectives
- Create databases.
- Understand how to create tables using datasheet view.
- Practice entering data into datasheets.
- Master the skill of importing data into tables.
- Explore filtering and sorting options in datasheets.
- Learn how to preview and print datasheets.
- Create queries using the simple query wizard.
- Create queries in Design view for more advanced functionality.
LEARN IT
INTERACTING WITH A DATABASE: DESIGNING AND CREATING TABLES
Now that we have grouped our data into tables, we can begin planning how to add the data into the tables. Important things to decide at this point are:
- plan what columns/fields will be in the table and what we should name them
- plan the data types we plan to associate with each column/field
- plan what data will be added to the tables themselves
Let’s plan the student table we used in the previous chapter. This table will contain all of the information about the students attending this school. Each row will contain the information for one student. In the table below, we decide what data type we plan to use for the column/fields in the Student table:
|
Column Name |
Data Type |
|
StudentID |
AutoNumber |
|
FirstName |
Short Text |
|
LastName |
Short Text |
|
City |
Short Text |
|
State |
Short Text |
|
Zip |
Number |
|
Major |
Short Text |
|
Class |
Short Text |
|
GPA |
Number |
For our ID, we can select the AutoNumber data type so that MS Access will automatically update this column for us whenever we add a new record. This is preferable since we don’t want to duplicate a Primary Key value. Next, we have our First Name, Last Name, City, State, Major, and Class columns. These will all contain a string of letters, making a word. Our word is not likely to exceed 255 characters, so we can use the Short Text data type. Long Text is used more for long descriptions or messages, not a few words. Finally, we have our Zip and GPA columns. These will be numerical, i.e., contain numbers, so we will assign those columns as the Number data type.
When we create the student table, we use the above design to add the fields with these names, and then assign them datatypes. Now let’s plan the Offering table. We will once again use the same table from our previous example.
This table will contain all of the information about the course offerings offered at this school for a given semester and year. Each row will contain the information for one offering. In the table below, we decide what data type we plan to use for the column/fields in the Offering table:
|
Column Name |
Data Type |
|
OfferingID |
AutoNumber |
|
StudentID |
Number |
|
FacultyID |
Number |
|
Grade |
Short Text |
|
Semester |
Short Text |
|
Year |
Number |
Since OfferingID is the Primary Key field for this table, we will assign it the AutoNumber data type. Next, we need to address our two “borrowed” fields, also known as our Foreign Keys.