CS 5 Computer Literacy
 | Introduction to Databases: Tables and Queries
Using Access
|
|
| Tables |
|
Tables
Microsoft Access organizes your information into tables. A table is made up of rows called records and columns called fields that look like a Microsoft Excel worksheet.
Every table has a topic. A table's topic is divided into categories that describe some aspect of the table's topic. These categories are called fields. For example, if the topic of a table is your customers, some fields the table would contain would be customers' last and first names. All the data stored in all the fields in one row of an Access table makes up one record. For example, a record might contain all the information you have about one item in your inventory.
A simple database might have only one table. However, most databases will contain more than one table. For example, you might have a table that stores information about inventory, another table that stores information about orders, and another table with information about customers.
To determine which columns a table needs, decide what information you need to collect about the table's topic. For example, for the Customers table, the First Name, Last Name, Address, City, State, Zip, and Email address would be a good starting list of fields. Each record in the table contains the same set of columns, so you can store the First Name, Last Name, Address, City, State, Zip, and Email address information for each record.
Specifying primary keys
Each table should include a field or group of fields that uniquely identifies each record stored in the table. The data stored in this field is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Access uses primary key fields to quickly associate data from multiple tables and bring the data together for you.
To read more about tables and database design, follow the link below:
Database Design Basics
| Field Properties |
|
Setting Field Properties
As you define each field in a table, you will also set field properties. Field properties include data type and optionally, formats and a description of the field's content. Each data type has a separate set of options available in the Field Properties portion of the Table Design window.
Data Types
For each field in a table, you make a selection from the data type’s list of options. Choose a field's data type appropriate for the kind of data to be stored in that field. For example a Price field that will hold dollar amounts would have a data type of Number or Currency while a Phone field would have a data type of text.
Field Size
Field size defines the maximum number of characters field values may contain for text, number and AutoNumber data types. A field's size should be big enough to hold the largest piece of data you expect to store in the field without making the size too large. Field sizes larger than they need to be waste disk space. For example, a State field with a size of 50 would be wasteful; a more appropriate value would be a field size of 2.
| Working With Records |
|
Add Records in Datasheet View
After creating a table, you can add records to it in Datasheet View. Newly entered records are placed in the order in which you enter them. When you close the table and open it again, the records will be ordered according to the primary key.
Editing Records
To edit a record’s field values, select the field value and type the new information.
Deleting Records
To delete a record, click the record (row) selector to the left of the first field value and either press the Delete key or click the Delete button in the Records section of the Home tab. To delete more than one record at a time, click and drag through the record selectors for all the records you want to delete and press the delete key. Remember, once you delete one or more records, the records are gone. You can’t undo deletion of records!
Sort a Datasheet
Sorting rearranges the records in a table or query datasheet. You select one or more fields to sort by and then choose ascending or descending order. If you want to sort by more than one field in a table, the fields must be side by side; you can’t sort on two or more fields if the fields you want to sort on are separated by other fields.
| Relational Databases |
|
A collection of related tables is called a relational database. Relations between tables are based on a field that is common to two of the related tables. For example, every record for every customer in a Customers table has a field that contains data that uniquely identifies each customer. Even if you had two customers named Jane Brown, each would have her own unique identifier to distinguish one Jane's order from the other Jane's order. This unique identifier is called a primary key. Every time a customer places an order, a record is created in the Orders table and the customer's primary key is added to the order information. The existence of this common field enables Access to associate or link a record in the Customers table with a record in the Orders table. It is this ability to create links between tables that makes a relational database such as Access a powerful tool.
| Redundant Data |
|
Data redundancy refers to the same data that exists in two or more tables. Data redundancy can lead to data that lacks integrity, that is, data that is inaccurate. Data redundancy is often caused by poor database design. For example, if you enter a customer's name, address and order information in an order table, each time the customer places an order you will have to re-enter the customer's name and address. Chances are good that at some point the customer's name or address will be entered incorrectly.
A better design for the database would be to keep the customer information in one table and the order information in another table. Every time the customer placed an order, the only piece of customer information that would go into the order table would be the customer's ID. This reduces the time it takes for data entry, reduces data redundancy and increases the chances that data in the two tables will have integrity, that is the data will be accurate.
| Queries
|
|
Queries
What Is A Query? A query is a question you ask about the data stored in a table such as which employees live in New York or which inventory items cost more than $100. When you run a query (ask a question about the data in a table) Access responds by displaying the data that answers your question, that is, only the records that have NY in the State field or which inventory items cost more than $100.
Queries are what you use when you need to compare weekly sales figures, track packages, or find all the members of your club who live in Texas. In other words, they're how you answer questions, and that makes creating queries an essential "databasing" skill.
To open an existing query, click Queries in the Navigation pane and then double click the query you want to open; the query datasheet appears. Query datasheets look identical to table datasheets with records displayed in a row and column layout.
If you want it to, a query can also process data. For example, you can find the sales for last month and calculate performance figures for each office. And you can do more than just find data. For example, you can use a query to add last month's inventory figures to your database. You can also use queries as data sources for forms, reports, and even other queries.
| Types of Queries
|
|
Types of Queries
Access provides several types of queries.
Select Query
A select query retrieves data from one or more of the tables in your database, or other queries there, and displays the results in a datasheet. You can also use a select query to group data, and to calculate sums, averages, counts, and other types of totals.
Parameter Query
A parameter query is a type of select query that prompts you for input before it runs. The query then uses your input as criteria that control your results. For example, a typical parameter query asks you for starting high and low values, and only returns records that fall within those values.
Crosstab Query
A crosstab query uses row headings and column headings so you can see your data in terms of two categories at once.
Action Queries
An action query alters your data or your database. For example, you can use an action query to create a new table, or add, delete, or change your data.
| Building a Query
|
|
So, how do you start building a query? The first step is to know the structure of your database, and the data in each table. You don't have to memorize every last record, but you do need to know where everything is. Open your tables and explore their fields and data.
Next, you design your query. An easy way to do that is to state the question you want your data to answer. The more detail you add to your question, the more precisely you can define your query. For example:
What's our most popular sales item in South America?
How many of my DVDs are on loan to friends?
Who's ordering the team jerseys this year, and what sizes do we need?
If it helps, write down your question and include as many field names as you can. For example: "I need the 10 best selling products in Malaysia. I need to know product names, product IDs, and the department that makes each item."
Once you have your question, you can build your query.
Two tools you can use to build a query are the Query Wizard and Design view.
The Query Wizard makes it easy to build queries that group your data, and that retrieve data from multiple tables.
Design view gives you complete control over your queries.
With either tool, you start by choosing a record source — the table or query that contains the data fields you want to see. After you the data source, you add the fields to your query and then run the query to test it.
You run a query whenever you open it. Whenever you run a query, it takes the latest data from your record source and loads the results into a datasheet. The data returned by a query is called a record set.
| Create a New Query
|
|
Create a New Query
Click on a data source, either a table or a query, in the Navigation Pane, click Create on the Ribbon and then click either the Query Wizard or Query Design. The Simple Query Wizard leads you through the steps needed to create your query. You can also create a query in Design View.
Queries may display all or only selected fields from a table. In the Simple Query Wizard dialog box, select which fields you want included in the query. If you create a query in Design View, add one or more tables (or queries) to the Design grid and then drag fields from the field list(s) into the grid.
Query Design View
Query Design view allows you to specify the tables and/or queries and the fields you want to use in your query and the criteria (conditions) used to control displayed records. Each design grid column represents a field in the query datasheet .
Add Fields to a Query
There are several ways to specify which fields you want added to the query design grid:
Double click a field in the field list
Drag a field to the design grid
Double click the title bar of a field list and then drag any field into the grid to add all the fields to the design grid
Viewing Query Results
After you have selected the fields and chosen the criteria, to see the results of a query, click the View button or click the Run button. Query datasheet records result from the fields and optionally, criteria added to the query design grid. Query results display in the table's primary key order unless you specify another sort order.
Saving a Query
To save a Query, click the Save button and then give the query a name.
Table Data Is Linked to the Query
Query results are linked to the associated data source. This is extremely important to keep in mind. Changing data in the query datasheet changes data in the underlying table!
| Query Criteria |
|
Criteria
Criteria is another word for conditions. Criteria control which records appear when you run a query. Criteria can be simple such as State=CA or complex including several fields and sets of conditions.
Exact Match Queries
One type of select query is the Exact Match query. Exact Match Queries display only records that match the criteria (conditions) specified in the query design grid. Values in the specified field must match the condition exactly. You enter conditions in the criteria row of the query design grid. When you run the Query, only records that meet the criteria are displayed. When you change the conditions and run the query again, the query displays a new set of records.
Query Operators
Criteria entered into the query design grid control which records appear in the query's datasheet. Criteria are often entered using one or more of three types of operators, Comparison, Special and Logical.
Comparison Operators
Comparison operators do for criteria what mathematical operators do for numbers. Just as you join two numbers with a math operator (5+5), you join a field and a condition with a comparison operator (Salary > 75000). There are six comparison operators.
= Equals =MA
<> Not equal to <>MA
< Less than <50000
> Greater than >2/1/2004
<= Less than, equal to <=60015
>= Greater than, equal to >=75000
| Quick Reference Card for Queries
|
|
Design a query
Write the question(s) you want to answer. In each sentence, include the name of each field you need to see, or at least a close approximation.
Run an existing query
In the Navigation Pane, under Queries, double-click the query you want to run.
If the query is open in Design view, go to the Design tab on the Ribbon, and in the Results group, click Run.
Open a query in Design view
In the Navigation Pane, under Queries, right-click the query and click Design View.
If the query is open, right-click its tab and click Design View.
Facts to remember about queries
Queries take their data from a record source — fields from tables, other queries, or a combination of the two.
Queries display their results in a datasheet. The result is called a record set. You can work with the data in a record set in the same ways you work with data in a table — add new data, change existing records, sort, filter, and so on.
You can alter queries until they produce the results you need.
You can use queries as data sources for forms and reports.
The Query Wizard and Design view provide the easiest ways to build queries.
| This Week's Assignment
|
|
The Microsoft Web site has extensive help files and even self-guided tutorials on all the Microsoft products.
The links below will take you to two lessons on queries:
Queries I: Get started with queries
Queries II: Create basic select queries
This week you will complete an assignment with Access that will introduce you to Access table and queries.
Click on the links below to download the files you will need for this assignment and save them to your computer's hard drive or to your flash drive.
Access Query Questions
Table and queries database
There are two files for this week's assignment. The first file (AccessQueryQuestions.docx) contains terms for you to define and questions for you to answer. Use the information from our class discussions and on this class Web page to help you define the terms and answer the questions. After you have defined the terms and answered all the questions, upload your work to our class drop box. The name of the file you submit should be AccessQueryQuestions.docx
The second file (CS5TablesQueries.accdb) is a database file we will work on together in the lab. Make sure you upload your database file to our class drop box before you leave the lab today.
