Access Chapter 2 (Reading)
Introduction to Creating Database Tables
The building blocks of relational databases are the tables that organize and store information within the database. In this section, you will learn how to create a append records to an existing database table, use the table to create various simple queries, create a simple form, and a simple report. This week is the broad overview to the four objects used repeatedly during the course.
Understanding Data and Its Accuracy
All the information on a person, place or thing is placed in a database table, categorized by fieldnames. There can be an abundance of information that can be stored in a table. Think about all the data that is stored on students, patients, customers, employees, etc. Data in each table really relates to what the file is storing, and the pieces and parts to the data.You will learn that Access can have multiple tables in one file. This helps keep the data organized into groups, VERSUS putting all the data in one table. Imagine if you could not have multiple tables allowed in a relational database like Access, you would have miles and miles of fieldnames, that seemed to be repeating and confusing for inputting and to query or report on. It is imperative that data in the tables is accurately typed in, spelled correctly and consistent and grouped into logical tables. For example, you may have a customer database file that has a customers table, a product table, and a orders table, all related by what a customer purchases during a order.
Data consistency is critical too. For example, if a user is keeping track of mailing information for a client, then State would likely be a fieldname use to record as one of the fieldnames. If multiple users input into this file, one using OR to represent Oregon, and the other user using ORE to represent Oregon, then data input is not consistent. The problem with inconsistent inputting, relates to queries and producing a list with states. The query user would need to remember to query for both OR and ORE, to receive accurate results back to the query. There are ways to prevent data differences, one being, limit the field size that can be input. Users can also set field to input in uppercase letters, or lowercase letters. Setting uppercase on a state field would be beneficial and prevent formatting differences
Types of Queries
Essentially, the ability to retrieve and analyze your data is completed by running a query. Queries produce a list like a spreadsheet showing results based on what was considered for for fields. Users can pull information from one or more tables based on a set of conditions the user defines. There are many types of queries available to a user, ranging from simple to advanced. During the term, various queries will be developed using data given. There are simple queries, parameter query (user inputs during query), append queries, delete queries, make table queries, And queries, OR queries, Like queries, Crosstab queries, Action queries and more types of queries to create and run to pull or update information in a table. For example, a user could create an update query, that updates records in a table based on certain conditions. Once designed and run, the table records would be updated.
Types of Forms
New data/complete records are often input using the table, which resembles a spreadsheet.
The user is able to see the previous records and data input that was input. Another way to input data into a table, is by a form. Forms show a user just one record at a time, with fields for inputting. Forms in "real life" can resemble forms used in business and recreated in the MS Access form. Theoretically, this would make inputting the data into a file, quicker and easier to follow.
Access allows for the simple creation of a form, split forms (showing information from two or more tables), forms with buttons, drop-down lists, yes/no checkboxes, subforms, and embedded tables.
Types of Reports
Reports are commonly used for printing data or presenting data in a reader-friendly, visually appealing format. Access allows easy to create and customize reports using data from tables, or queries. Labels are also considered reports in Access, so if you need to produce mailing labels for a large mailing, you can create a report, using labels as the format/layout and design.
This week, you will be introduced to creating each of these objects in their most basic capacity. In future weeks, more in-depth design and application of these objects will be utilized.