Download Mucrosoft access 2007 powerful application software and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!
A Tutorial on MS Access
Dongqiuye Pu pu@email.unc.edu
Xiangyu Fan xyfan@email.unc.edu
Weimao Ke wk@drexel.edu
School of Information and Library Science
University of North Carolina at Chapel Hill
Oct 23th, 2012
1. Goals
This tutorial is for students to learn and practice basic database operations with MS Access. Particularly, after the tutorial, students will know the basics of:
How to define tables in MS Access; How to create forms for data browsing and data entry; How to customize a form for better user/data interaction; How to create SQL/queries for search and retrieval.
2. Data
In this tutorial, we will work on two tables, i.e., Department and Employee, as shown in Figure 1. There is a one-to-many relationship between Department and Employee. That is, a department can have multiple employees while each employee belongs to one department (through the foreign key DNO).
Figure 1: Two Tables
- Database Creation
3.1. Start MS Access
To start Access, select START -> All Programs -> Microsoft Office -> Microsoft Office Access 2007.
3.2 New a Database
Now follow Figure 2 (a), (b), and (c) to create a new blank database. Please close the Table1 automatically created (shown in Figure 2 (c)).
(a) To new a database (b) Database name & path (c) Database created
Figure 2: New Database
- Table Definition
Now follow Figure 3 (a), (b) to create and define a new table, e.g., the Department table. To define a primary key, select the filed/attribute you want and click the Primary Key button, as shown in Figure 3 (b).
(a) To design a table (b) Table Design View
Figure 3: Table Definition
To decide what data type you should use for each field, look at Table 1 for basic data types in MS Access and Table 2 for various Number types in Appendix.
e.g., for referential integrity (foreign key constraint) and cascading update/delete (triggers). Please check the Enforce Referential Integrity option only and click the Create button. Figure 6 (b) shows a defined relationship between Department and Employee. Click the Save button or press Ctrl + S to save. Close the Relationships view.
- Forms
Form is a useful tool for data browsing and manipulation. It provides various tools for the design/customization of user-data interactions. Forms can run on Tables directly, or on Queries, which we will discuss in Section 7.
6.1 Form Basics
The easiest way to create a form is to use the automatic form tools or wizards. As shown in Figure 7 (a), select the table (or query) you want the form to be based on and click the Create -> Form button. Figure 7 (b) shows a form thus created based on the Department table. Click the Save button or press Ctrl + S to save the design. Click the View button (to the very left) to browse existing data. Note that on the Department form just created, related Employee records are also attached – the system knows the one- to-many relationship we defined earlier. Once a form is opened, you can switch between three views by using the View button, e.g., the Form view (which shows data and allows user interactions), the Design view (which allows you to redesign the form), and the Layout view (which offers both interaction and design functionality).
(a) To define a Relationship (b) Relationship defined
Figure 6: Relationships of Tables
(a) Form wizards (b) Form created
Figure 7: Creating Forms
6.2 Form Customization
Figure 8: Form Design View
Forms provide much richer tools for user-data interaction. For example, in the Employee table, we use data type Yes/No for the Sex field. Technically, this is okay and saves data space. However, it is not intuitive (and not making sense) to use Yes/No to represent Male/Female in the data view.
A better alternative to this is, without changing the data type, to show Male/Female to the user and to allow the user to select Male or Female in data entry. Now let’s see how we can use Form to do this.
- select the Employee table in the list of tables;
- select the Create tab and then the Mulitple Items form button;
(a) Step 1 (b) Step 2
(a) Position (b) Two column display (improvement needed)
(c) To hide the first column (d) Final display
Figure 10: Combo Box: Final Adjustment
Bound Column: 1
Column Count: 2
Column Widths: 0”;1”
Now when you switch to the Datasheet View or create a new form based on the table, the system will automatically use a Combo Box (with Male/Female labels) for the Sex field.
- SQL Queries
Now let’s assume that we need to retrieve all employees (Names and SSNs) in the department named Research, in the order of their last names.
To create a query, click Create -> Query Design. Add both Department and Employee tables to the query design view. Because we’ve already defined the relationship between Department and Employee, a JOIN statement is automatically generated (i.e., the two tables are connected, as shown in Figure 11 (a)). Drag fields (to project Department.Dname, Employee.Fname, Employee.Lname, Employee.SSN) to the grid area. Put “Research” in the criteria for the Dname field and set the Sort property of the Lname field to “Ascending.” (see Figure 11 (a)) for detail.
After query definition, you can click the Save button or press Ctrl + S to save it. Now you can switch between multiple views, as shown in Figure 11 (b). To see/edit the SQL script of the query, click SQL View and the codes will be shown (Figure 11 (c)).
To run the query, click the Datasheet View shown in Figure 11 (b).
- Documentation
To document all you have created, click Database Tools -> Database Documenter. As shown in Figure 12, you can select tables, queries, forms, and relationships (under the Current Database tab) to generate a report. When the report is shown, you can export it to various formats including RTF (as shown in Figure 13).
(a) Query Design (b) Views (c) SQL View
Figure 11: Query Definition
Figure 12: Database Documenter
Appendix
Setting Type of Data Size
Text
(Default) Text or combinations of text and numbers, as well as numbers that don’t require calculations, such as phone numbers.
Up to 255 characters or set by FieldSize, whichever is less.
Memo Lengthy text or combinations of text and numbers. Up to 63,999 characters.
Number Numeric data used in mathematical calculations. 1, 2, 4, or 8 bytes
Date/Time
Date and time values for the years 100 through
8 bytes
Currency
Currency values and numeric data, accurate to 15 digits on the left side of the decimal separator and to 4 digits on the right.
8 bytes
AutoNumber
A unique sequential number or a random number for a new record. Can’t be updated.
4 bytes
Yes/No Yes and No values (Yes/No, True/False, or On/Off). 1 bit
Table 1: Some Data Types in MS Access
Setting Description Decimal precision Storage size
Byte
Stores numbers from 0 to 255 (no fractions).
None 1 byte
Decimal
Stores numbers from − 1028 − 1 through 10^28 − 1 (.mdb,.accdb)
28 2 bytes
Integer
Stores numbers from −32, 768 to 32, 767 (no fractions).
None 2 bytes
Long Integer
(Default) Stores numbers from −2, 147, 483, 648 to 2, 147, 483, 647 (no fractions).
none 4 bytes
Single
Stores numbers from −3.402823E38 to −1.401298E − 45 for negative values and from 1.401298E − 45 to 3.402823E38 for positive values.
7 4 bytes
Double
Stores numbers from −1.79769313486231E to −4.94065645841247E − 324 for negative values and from 4.94065645841247E − 324 to 1.79769313486231E for positive values.
15 8 bytes
Table 2: Number Types in MS Access
