User Services
OBJECTIVES
Instructors
Microsoft
Access 2003 ® II
Harriet Foley Cesar Zamora
Develop Field Properties
Import Data from an Excel
Spreadsheet
Create Relationships
Create a Form with a Subform
Create Action Queries
Create Command Buttons
Create a Switchboard
Create a Macro
Introduction
U
se the new Microsoft Access 2003 Task Pane t o f i n d W h a t ’s N e w i n Microsoft Access 2003. For example, view information on object dependencies, error checking and backing up your database.July 2007
Microsoft Access 2003 is a powerful tool to organize, manipulate and present data. Topics will include developing field properties, importing an Excel spreadsheet and MS Access tables, creating a lookup table and working with the Relationship Builder. Also included will be in-depth coverage of queries and forms, working with relational queries, creating a Master Switchboard and a macro. These exercises will allow participants to accomplish enhanced data analysis.
The ITS Help Desk is a service provided to all Mississippi State University students, staff, and faculty. The ITS consultants are available to help with various computer-related problems as well as provide answers to computer and technology- related questions. Visit the Web site at www.its.msstate.edu for handouts and resolutions to common computer problems.
If you cannot fi nd an answer to your question on the Web or you do not have access to the Internet, please call at 325-0631 (7:30 a.m. to 5:00 p.m. Monday through Friday). You may also contact the ITS Help Desk at 108 Allen Hall or by email at [email protected].
Getting Help
User Services
Table of Contents
Microsoft Access 2003 Interface and Windows...
Creating Database...
Import Data...
Default Date...
Relationships...
Lookup Table...
Relationship Builder...
Relational Query...
Form with Subform...
Action Queries...
Make-Table Queries...
Delete, Append Query...
Basic Forms...
Advanced Forms...
Switchboard Manager...
Macros...
PowerPoint Slides...
3 9 10 12 13 15 16 21 22 25 26 28 30 31 33 35 37
Table of Contents
User Services
A database is the container for your data and associated objects. It includes tables, queries, forms, reports, pages, macros and modules. When Access is open you are working in one database, but each database can contain many tables, queries, forms, etc.
Title Bar Menu Bar
Tool bar
Open Database Print Spelling Undo Analyze Relationships Help
Microsoft Access 2003 Title Bar, Menu Bar and Tool bar
Database Window
Default Group Category Button
Database Window Tool bar
New Database Save Print Preview Format Word, Excel Large Icons New Object
Database Window
Objects Menu New Object
Shortcuts
List Area for Objects
(table names, query names,
form names, report names)
User Services
Table Window
A collection of data about a specifi c topic. Tables organize data into rows called records and columns called fi elds. Records and fi elds combined make up the table.
Design View
Field Name Column Data Type Column Description
Row Selector
Field Properties
Data Types
Text - 0-255 ch - Default Memo - 0-64,000 ch Number - Numeric values Date/Time - Date and time data Currency - Monetary data
Auto number - Automatic Number increments Yes/No - Logical Values , also True/False, On/Off OLE object - Pictures, graph, sound, video Hyperlink - Link to an Internet source
Lookup wizard - Displays data from another table.
Field Properties
Field Size - Text - limits size of fi eld to specifi ed ch.
Numeric - Allows specifi cation of numeric type Format - format data
Input Mask - Sets data entry into a predefi ned format Decimal Places - Sets number of decimal places
Caption - Replaces fi eld name on form and report if used.
Default Value - Fills in automatically on data & form entry if placed here.
Validation Rule - Data is validated based on rules placed here.
Validation Text - Message displayed if Validation Rule broken.
Required - Specify if data must be entered.
Indexed - Speeds up data access and can limit data to unique values.
Column
Tables
User Services
Field Names
Record selector Space for 1st
record
Record Navigation
Buttons
Previous Record Last Record
Table Window in Datasheet View
Summary of Views
Table Views
Query Views
Form Views
Report Views 1st Record
---
Tables
Current Record Next Record
New Record
User Services
Form Window
The form is where you normally enter, display and edit your data. It provides greater fl exibility than a table. As you enter data into a form you are
simultaneously adding it to the table.
Most forms just have a detail section, but a form can also have form header, page header, page footer and form footer.
Form View
Design View
Forms
User Services
Query Window
A query is either a question about the data stored in your tables or a request to perform an action on the data.
Design View
DataSheet View Field List
Pane can be resized with mouse
Criteria for fi eld Show Check Box
Query Design Grid
Results of Query
Queries
User Services
Reports
Design View Print Preview
Report Window
Reports give you control in presenting your data. They are exceptionally good for sorting
and grouping data and for providing subtotal and summary calculations.
User Services
In todays workshop we will learn several advanced features of Microsoft Access. First we will create a new database, then we will import one Microsoft Excel table and two Microsoft Access tables into it. The data will already be entered in these tables.
1. Open Microsoft Access 2003. In the Task Pane select Create a new fi le.
2. Under New select Blank database.
Create Database and a Table
3. In the File New Database dialog box select Desktop for the Save in: box.
Type Advising in the File name: box.
4. Click Create.
Open Microsoft Access 2003
User Services
Importing A Data table into Microsoft Access from Microsoft Excel
1.
2.
3.
a.
b.
c.
e.
f.
4.
Data can easily be imported into Microsoft Access. From the File pull-down menu select Get External Data and Import.
In the Import dialog box change Files of type: to Microsoft Excel. In the Temp folder on the Desktop double click on the Major Excel fi le. After this Excel fi le is imported into MS Access it will become a lookup table.
In the Import Spreadsheet Wizard:
Select Show Worksheets and Next.
Select First Row Contains Column Headings and Next.
Accept the defaults. Click Next.
Click on Choose my own primary key and select ID, if not already chosen. Click Next.
Name the table Major. Click Finish.
The Design View and Data Sheet View of the Major Table will appear as follows:
Close the table.
Import Data
d.
Keep In a New Table. Click Next
User Services
5. Import the Visit and Student Information tables. From the File pull-down menu select Get External Data and Import. In the Import dialog box change Files of type to: Microsoft Offi ce Access. Select MSU102 MS Access database from the Temp folder on your Desktop. Click Import. Select Visit Information and Student Information tables. Click OK. The design view and datasheet view of these tables will appear as shown below. A relationship will be created between the primary Student Information table and the secondary Visit Information table.
Import Data
Importing Data Tables into Microsoft Access From Another Database
Visit Information
Student Information
User Services
5. Click the “=” sign .
6. Double-click the “+” next to Functions.
7. Single-click Built-In Functions.
8. Select Date/Time in the middle box.
9. Double click Date in the right-hand box.
10. Click OK.
The box that is displayed is called the Expression Builder.
11. Close the table.
The Date setting will put the current date into the Updated fi eld as the default value. The user can override this entry and type another date if desired.
1. Open Student Information in Design View.
2. Select the Updated fi eld.
3. Click in the Default Value box.
4. Click the Build button.
Default Date
Default Date
A default date will be added to the Student
Information table. The Student Information
table already has a mask for Date of Birth of the
students.
User Services Relationships
There are two reasons to set relationships between tables. Either for a table lookup or to handle multiple occurrences of data.
Table Lookup
A fi eld in one table can be used to look up data in another table. This keeps from having to store the same data in multiple places. Only the key fi eld from the lookup table is inserted into the main table.
If data is updated in the lookup table it will be updated throughout the table(s) it is linked to.
Another reason to set a relation is to handle multiple occurrences of data. In our system design, there are multiple occurrences of visits that each student has had to the Student Health Center.
Information about each visit should be entered on a separate line and in a separate table from the demographic data about each student. The two tables could be linked together by a fi eld such as the students NetID. This prevents duplication of the demographic data in tables.
You want to limit repetitive data entry. In place of large tables with duplicated data, you create smaller separate tables. You join them only when you need to. Queries are used to join the tables together.
_________________________________________________________________________________
Normalization
Normalization is the process of achieving the optimum design in a database. This makes the database easy to manipulate and search, as well as helping to insure data integrity. The three most common steps of normalization are First Normal Form, Second Normal Form and Third Normal Form.
First Normal Form is the most basic level of normalization. To meet the requirements of this level, each of the column (fi eld) values must be atomic (store a single value.) If your database has a fi eld for courses and lists two or three courses in each fi eld it is not in fi rst normal form. In addition, groups of columns cannot be repeated, so your table could not have “visit1”, “visit2”, etc. and be in fi rst normal form.
Advantages of a Relational Database
Multiple Occurrence of Data
User Services
There are three types of relationships in Access: One-to-Many, Many-to-Many and One-to-One.
One-to-Many
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
Many-to-Many
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.
This type of relationship is only possible by defi ning a third table (called a junction table) whose primary key consists of two fi elds - the primary key from both Tables A and B. A many-to-many relationship is really two one-to- many relationships with a third table.
One-to-One
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is rarely used, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fi elds, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table, for example, a second address.
Relationships
Relationships
User Services
Look up Table
1. Open the Student Information table in Design View. Click in the Major row and select Lookup Wizard... from the drop down box in the Data Type column.
2. In the fi rst dialog box of the Lookup Wizard select I want the lookup column to look up the values in a table or query.
4.
5.
6.
In the next dialog box select all fi elds. Click Next. In the following dialog box select Major for What sort order do you want for your list? Click Next.
In the next dialog box of the Lookup Wizard leave the Hide key column checked. Click Next.
Leave the title Major in the text box for What label would you like for your look-up column?
Click Finish.
7. Click Yes when the next dialog box asks The table must be saved before relationships can be created. Save now? Assign majors to your students in datasheet view. Close the table.
Lookup Table
3. Choose Table: Major in the next
dialog box. Click Next.
User Services
Defi ning Relationships
Relationships can be created in queries or at the table level. Unless you do not want your relationships to always exist, you should create your relationships at the table level. You can use the Relationship Builder to do this.
1.
2.
Click the Relationships icon to open the relationship window. You will see the Lookup relationship that we just created.
Click the Show Tables icon on the toolbar to add tables to the window.
3. Click on Visit Information followed by the Add button to add the Visit
Infomation table to the Relationships window. The three tables shown below should be in the Relationships window.
Click on the Close button in the Show Table window.
4. Next we want to create the relationship for each single record in the Student Information table that links to multiple records in the Visit Information table. We will set up a one-to- many relationship. Each student in the Student Information table can have many visits in the Visit Information table. To link tables, the primary fi eld from one table is linked to a foreign key fi eld in the secondary table.
Relationship Builder
User Services
5. To create the relationship between the primary key NetID in the Student Information table (the primary table) and the foreign key, NetID, in the Visit Information table (related or secondary table), click on NetID in the Student Information table and drag it to NetId in the Visit Information table. The two fi elds did not have to have the same name, but they do need to be the same type.
6. The Edit Relationships dialog box opens. This is a one-to-many relationships. There is only one NetID and record for each student in the Student Information table, while in the Visit
Information table there are many NetID’s and records referring to the same student. After the relationship is set up, queries can be created that draw from both tables.
Check Enforce Referential Integrity. The user will be prevented from entering a record in the Visit Information table if there is not a matching NetID record in the Student Information table.
Referential Integrity is enforced based on the key fi elds. When Enforce Referential Integrity is checked, Microsoft Access won’t let you create orphans in your secondary table.
Select Cascade Update Related Fields.
By doing this changes in the primary key in the Student Information table will automatically be transferred to the Visit Information table.
Click on the Join Type button.
Relationship Builder
User Services
Your choice here will infl uence query results. Click on the third and most popular choice. This will include all records from the Visit Information table and only related records from the Student Information table. Click OK.
7.
8. Click Create to create the relationships between the two tables.
9. Now in the Relationships window, a new line is visible, displaying the one-to-many relationship.
The one is on the Student Information, primary table side and the many is on the Visit Information, related table side.
Relationship Builder
10. Close the Relationships Window.
User Services
1.
Cascade Update Related Fields
Open the Student Information table and your Visit Information table and place them side by side.
1. Try entering a record with the NetID abc1 in the Visit Information table. Access won’t let you enter it because it doesn’t exist in the primary table. Referential Integrity is enforced based on the key fi elds. When Enforce Referential Integrity is checked, Microsoft Access won’t let you create orphans in your secondary table.
Enforce Referential Integrity
2.
3.
Close the Visit Information table
Relationship Builder
Open the Student Information table and add a record with NedID abc1. Fill in the other fi elds with data. Close this table.
4. Return to the Visit Information table, Open View, and enter a record with NetID abc1. This
student can now be entered in the related or secondary table.
User Services
Change jtt2 to aaa5. When you move to the next record in the Student Information table the changes will automatically be transferred to the Visit Information table. Change aaa5 back to jtt2.
2.
3. Close the two tables.
Relationship Builder
User Services Relational Query
Automatically Filling in Data From One of Two Joined Tables
1.
If a query brings in data from two or more tables, some of the fi elds can be automatically fi lled in. When setting up the query, select the foreign key of the linked fi eld rather than the primary key. The fi elds from the Primary table will be automatically fi lled in. A form can be based on this query and the fi elds will be automatically fi lled in also.
In the Database window click on the Queries tab. Double-click Create query in Design View. Add the tables Visit Information and Student Information to the Query grid. Close the Show Table dialog box.
Open the query and fi eld windows so you can see all the variables.
Visit Information VisitID
Date Time Diagnosis
From the Visit Information table add NetId, the foreign key with the many symbol next to it. From the Student Information table add First_name, Last_name, Major and City. Return to the Visit Information table and add VisitId, Date, Time and Diagnosis.
Student Information First_name
Last_name Major City 2.
Visit Information
NetId
User Services
Form with Subform
Creating a Form with a Subform
1.
2.
Click on the Forms tab. Double-click Create form by using wizard.
Select Table:Student Information and move all the fi elds to Selected Fields.
Select Table: Visit Information and move all the fi elds to Selected Fields. Click Next.
Run the query.
A form created from this query would also automatically fi ll in fi elds from the Student Information table. The new data on Visit Information would also be automatically entered into the Visit
Information table.
3.
4. Add new information to the query window, datasheet view, using an existing NetId. Add the NetId jtt2 and click the tab key. The information from the Student Information table is automatically fi lled in the query window. Add new data for the visit. The new data on visit information is automatically populated to the Visit Information table.
5. Close the query and save it.
User Services
3. View your data by Student Information. Leave the default Form with subforms. Click Next.
4. Select Datasheet for the Layout of your subform. Click Next.
5. Choose a style and click Next.
Form with Subform
User Services
6. Click Finish.
7. Now you can view your main form with a subform on it. Notice there are two navigation bars.
Close the form when fi nished testing it.
Form with Subform
User Services
Action Queries
The action queries are: Delete, Update, Append and Make Table. The next three queries we will look at are the Make Table, Delete and the Append queries. These three queries are often used together to provide a very useful function: removing outdated records and storing them in an archive table. This is called Archiving Records.
We will follow a three-step process: fi rst we will use a Make-Table query to create a table.
Second, we will use a Delete query to remove the records we copied from the fi rst table. Finally, we will create an Append query to copy records from our main table to our archive table in the future.
Action Queries
Our fi rst step will be to create a Make-Table query.
Make-Table Query
1. Make sure all tables and forms are closed.
2. Select the Queries object in the Database window.
3. Click New.
4. Select Design View.
5. Click OK.
6. Add Student Information table to the Query window.
7. Close the Show Tables window.
There are two types of queries in Access: Select and Action. Select queries display data and action queries alter data.
8. Add all of the fi elds to the Query grid. Double-click the title bar
of Student Information. Click and drag the highlighted fi elds to
the fi rst column and release the mouse button. Notice that Access
spreads out the fi elds automatically.
User Services
We will archive all records prior to January 1, 1999.
9. Locate the Criteria cell of the Updated fi eld.
10. Type <1/1/1999 in the Criteria cell.
11. Click Query on the Pull-down menu.
12. Select Make-Table Query.
Type the name tblArchivedRecords in the Table Name: text box. This will be the name of the table with archived records.
Notice that you can save the table to another database to help keep the size of this database small.
Click OK.
Close the query without running it.
Say Yes to save changes to the set up of the query.
Make-Table Query
13.
14.
15.
16.
17.
User Services
18. Name the query qryMakeArchive.
19. Click OK.
Now we will run the query to make another table identical to our original table, but only containing records that meet the criteria specifi ed in the Updated fi eld of the criteria row.
20. Double-click the query, qryMakeArchive in the Queries object window within the Database window.
22. The next warning window will tell you how many records are going to be pasted. A message warns you there is no undo. (But you can always delete the new table.) 23. Click Yes.
24. Switch to the Tables object in the Database window.
25. Open the tblArchivedRecords table and verify that there is one record.
26. Close the table and click the Queries object.
27. Open qryMakeArchive in Design view.
Make-Table Query
21. Click Yes in the warning window.
User Services
Delete Query
Delete, Append Queries
With the Archive table created and our initial record copied it is time to move to the second step:
deleting the copied record from the original table. The Delete query can be a very dangerous query in that it will destroy information permanently. We will not save this delete query.
1. With the Make-Table query in Design View, click Query in the pull-down menu.
2. Select Delete Query.
3. Run the query by clicking the red exclamation point.
4. Click Yes in the warning box.
The records have now been deleted from table Student Information. Because this Delete query can cause problems if run accidentally we will not save it.
Append Query
Now we will create a query that will append records to our table in the future.
1. Click Query on the Pull-down menu.
2. Select AppendQuery.
3. Leave tblArchivedRecords as the Table Name:.
This is the table we will be appending records to, the archived table.
4. Click OK.
User Services Append Query
We will now add a parameter to the query so that we can set the date range for the archived records:
5. Locate the Criteria cell for the Updated fi eld.
6. Delete the 1/1/1999.
7. Right click in the cell, click Zoom. Type
Between [Beginning Date:] And [Ending date:]Click OK.
8. Click File on the Pull-down menu.
9. Click Save As...
10. Name the query qryAppendStudents.
11. Click OK.
12. Run query qryAppend Students and test it with todays date.
To complete the archiving of records, you
would set up a Delete Query. The delete
query would be run each time after running
qryAppendStudents to delete those students
who were just archived.
User Services
Basic Forms Basic Forms
7. Enter the following record using the form (try to enter data incorrectly and you will notice that our masks and validation rules still apply):
1. Make sure all the tables are closed and click the object Forms in the Database window.
2. Double-click Create form by using wizard.
3. Select Student Information in the Tables/
Queries text box.
4. Click >> to select all of the fi elds and click Next.
5. Select Columnar and click Next.
6. Select Expedition (or another style if you prefer) and click Next. Change the name of the form to frmStudentInformation.
8. Close the form. Click on the Tables object. Open the table Student Information and check out
your new record. Close the table.
User Services
We will add controls to our form. Up until now we have used forms to enter data into tables.
Controls increase the usability of forms. Using the Command button you can add record navigation, record operations, form operations and report operations to your forms.
7. Click and drag in the Detail section of your form to make a small rectangle. The Command Button Wizard will open.
1. Go back to the Database window.
2. Select Forms.
3. Open the form
frmStudentInformation.
4. Switch to Design View.
5. Maximize the design window and stretch the form to fi ll the window.
6. Click the Command button icon on the toolbox.
8. Select Record Navigation in Categories 9. Select Go to Next Record in Actions.
10. Click Next.
11. Click the Picture radio button, select
one of the pictures by selecting its descriptive text. Click Next.
Advanced Forms Advanced Forms
Record Navigation - Go to Next Record
User Services
12. Name the button cmdNext.
13. Click Finish.
14. Perform the same steps to add a Previous button to your form. Name it cmdPrevious.
Advanced Forms
Record Navigation - Go to Previous Record
15. Place a third command button on your form.
16. Select Form Operations in Categories and Close Form in Actions.
17. Select a picture and name the button cmdExit.
18. Switch to Form View and test your buttons.
Form Operations - Close Form
19. Switch to Design View, click the Command button icon and draw another rectangle on your form.
20. Select Record Navigation in Categories and Find Record in Actions. Click Next.
21. Click the text radio button and type Search in the text box. Click Next.
22. Name the button cmdSearch and click Finish.
23. Switch to Form View and test the search button by fi rst clicking in a fi eld and then clicking Search. Notice that the button allows you to search in whichever text box on your form has focus. Close your form.
Record Navigation - Find Record
User Services
A switchboard is a special type of form. Switchboards can be set up so persons who are unfamiliar with Microsoft Access can still use your database. On a switchboard, buttons are placed on a blank form. The buttons give the user instant access to tables, forms, queries and reports.
Switchboards can also be created using the Switchboard Manager.
1. Click on Tools, Database Utilities, Switchboard Manager.
2. When the following message appears, click on Yes.
3. In the Switchboard Manager dialog box, click on Edit.
Switchboard Manager
User Services
Switchboard Manager
4. In the Edit Switchboard Page dialog box, change the Switchboard Name to Student Advising. Click on New.
5. In the Edit Switchboard Item dialog box, fi ll in and select options as shown. Click OK.
6. Once more click on New in the Edit Switchboard Page and Add a command to Exit Microsoft Access. Click OK. Close the Edit Swithboard Page and Close the Switchboard Manager.
7. Test out your new Switchboard.
Note:
To have your switchboard open automatically when opening your database, from the
Tools pull-down menu select Start up... In the Display Form/Page textbox, select
Switchboard.
User Services Macros
Macros
Computers are great at repetitive tasks. In Microsoft Access a macro can be set to run repetitive tasks. One macro can run a series of commands. Once the macro is set up, it can be also be assigned to a button on a form. Macros can be used to open a form in edit mode, add mode or read only mode. You can use a macro to open or print a report or to close a form. A macro can be used to launch the main switchboard form.
Macros can also be created to respond to form events, some action of the user on the form such as when a user tries to delete a record, the macro will automatically be started and ask the user to verify the deletion of a record.
We will show you a simple macro that opens a form and a table.
1.
2.
3.
Click on the Macro object. Click on the New button.
Click on the drop-down arrow in the Action column. Choose Hourglass.
In the next row of the Action column select
OpenForm. Next to Form Name in the lower half of
the Macro dialog box under ActionArguments choose
frmStudentInformation.
User Services
4.
5.
6.
7.
In the next row of the Action column select Open Table.
Select Student Information for the Table Name under Action Arguments.
For the next Action choose Beep.
Close the Macro window and select Yes when asked if you want to save changes to the design of macro Macro1 ? Name the macro Beep. Test out the macro by running it.
Close the form and table. Close your database.
Macros
Note:
If a macro is named AutoExec, it will be
the fi rst element run when the database is
opened.
User Services
PowerPoint Slides
User Services
PowerPoint Slides
User Services
PowerPoint Slides
A publication of ITS User Services, a division of Information Technology Services,
Mississippi State University
Mississippi State University does not discriminate on the basis of race, color, religion,
