Previous
Jan's Working with Databases
Intro: Relational Database
A relational database uses several linked tables to hold data rather than in a single table. All of the tables and other database objects like forms and reports and queries are saved together in a single file. Such a file can get large quickly!
Goal: Design your tables so you can enter data in just one table and then use it in multiple records.
Example: Our example is an Access database, LanguageArtsClasses.accdb 
, that
I created when teaching the last 65 days of three 7th grade language arts
classes. The names have been changed to protect the innocent and the
guilty! (Yes, it was a long 65 days!) The assignments and grades are
real. So much work!
More recently many school systems have purchased software to do this for the whole school system. That's good IF the creators of the software included some flexibility. Can you enter bonus points that are based on the results of the robots race? Will the averaging formula choke if the result is more than 100%?
Examples will also be pulled from this database in later lessons. The
whole database can be downloaded for you to play with. It's in the resource files .
Example: Relational database for Language Arts classes (Access 2010)
Primary Goal: To track assignments and scores for students in order to calculate the six weeks averages in Reading, Spelling, and English and the averages for the year for each student.
Secondary Goal: To create reports for missing assignments to know how many copies of the make-up work to print and which students needed them.
I have tweaked the original database but it could definitely be improved even more! It does show most of the important features of a relational database and what you can do with a database. Most of what I wanted from this database could not be done in a flat file database, or, at least, not without an unreasonable amount of effort.
Tables and Relationships
A relational database includes two or more tables that have a relationship. Two tables are related when they have a field in common.
The shared field is the primary key of one of the tables and is a foreign key for the other table. A primary key must be a field that is guaranteed to have a unique value for each record in the table.
Most tables have an automatically assigned number as the primary key. Sometimes you have another choice, like a part number or a serial number or an ID number.
MS Access shows these relationships visually in the Relationships window. Lines connect the
related fields. The primary keys are marked with a key icon
. The StudentID was the id number assigned by the school system for their own records. The other primary keys were AutoNumbers, generated by Access automatically as each record was added to the table.
The Language Arts Classes database actually has more tables than this, but these are the most important ones.
Language Arts database, relationships between tables
Queries
A query can organize the data from one or more tables. It can sort the records into a particular order or filter results to show only the records that match certain criteria. Calculated fields in a query can combine text fields or do math with number values.
Design View
Nowadays queries are often designed visually. You can drag the fields that you want to see in your results from the table to the columns in the Design View. Choose which columns to sort on and add restrictions as criteria. You can even create new calculated fields based on the original ones.
The example query above is a simple one. It selects a few of the fields in the table Students. It creates a new field called Name by concatenating (putting two of more values together to form a single text string) the fields LastName and FirstName. The results are filtered to show only the students in class A. The results are sorted in ascending alphabetical order on the field Name. (Since the field Class is restricted to just class A, the Sort Ascending command for the field Class does not actually change anything.)
Datasheet View
When you run the query, the results show in a datasheet, neatly in order and filtered and only the fields that you chose will show.
SQL View
Behind the Design View, shown above, is programming code in SQL (Structured Query Language). You can view and edit the actual query code, if you choose. Once you know how to write a query in SQL, it can be faster just to type simple queries yourself (if your fingers will type what your brain meant to type). Sometimes that is a big 'IF'! For the most of us, drag and drop is a great blessing!
Project 2: Access Basics will introduce you to MS Access in detail, so we will not try to work with an actual relational database at this point.
If you would like to dive in and play around, there are some sample databases in the resource files for this course.
Sample databases:
- Christmas Ornaments Collection
Simplest example database: one table, one query, one form, and one report. Used in Exercise Access 4-2. - Star Wars Collectibles
Five tables, two queries, 9 forms including a switchboard form, 20 reports. Used for illustrations. - Language Arts Classes
17 tables, many queries of different types, many forms and reports, plus two macros. Used for illustrations. - Day Camp
Four tables, 13 queries, 7 forms including a switchboard form, 17 reports, one macro, and one module. Used for illustrations.
Northwind Traders sample database: From inside Access, you can open or download a template from Microsoft that is actually a full database for a fictional company, Northwind Traders. This is a complex database with many tables, queries, forms, and reports. It also includes two macros and several modules.
Access 2007, 2010: The Northwind 2007 database is already installed in the Sample or Sample Templates category that you see when starting a New database. You may have a short wait while Access configures the file for use.
Access 2013, 2016: The Northwind database is not installed with Access 2013 or 2016. When you create a New database, search online for Northwind when starting a New database. You should find Northwind 2007 or a similar name. Be sure to type in a name and select a folder to save it in. Then click on Create to download. The database will open and will be available from a local copy afterwards.
In all versions you will need to Enable Editing. Security settings block downloaded files from operating freely.
|
|
| © 1997-2017 Jan Smith All Rights Reserved |
Site Map What's New |
Privacy Policy Terms of Service Copyright Acknowledgements |
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~
Last updated: March 27, 2017