Introduction to Information Systems
Lab 08: Database using Microsoft Access III
Learning Outcomes
1. Experience how to create and modify Forms.
Copyright By PowCoder代写 加微信 powcoder
2. Experience how to create and modify Reports.
Introduction
A form in Access is a database object that you can use to create a user interface for a database application. A “bound” form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source. Alternatively, you can create an “unbound” form that does not link directly to a data source, but which still contains command buttons, labels, or other controls that you need to operate your application.
Reports offer a way to view, format, and summarize the information in your Microsoft Access database. For example, you can create a simple report of phone numbers for all your contacts, or a summary report on the total sales across different regions and time periods.
There are three types of forms that you can create in Access: Form, Multiple Items, Split Form Form: Display a single record at a time
Multiple Items: Display multiple records
You can use the datasheet portion of the form to quickly locate a record, and then use the form portion
to view or edit the record.
Introduction to Information Systems
Split Form: Display Form view and Datasheet view at the same time
Example: Create a Form for MemberInfo table
Part I: Edit the Layout of the Form
1. Open the file lab08_Forms_Reports.accdb which can be downloaded from CANVAS.
2. There is one table named MemberInfo. Double click MemberInfo to review the table content.
3. We would like to create a user-friendly form for MemberInfo table, so that user can view, edit and delete the record easily.
4. Click CreateForm, MemberInfo form will appear.
5. In order to create a user-friendly form, we would need to change the text into an easy
understandable wording in the form within the Layout View.
MEM_ID Member ID MEM_Gender Gender MEM_Regions Living Region
MEM_Last_Name Last Name MEM_Month Birthday Month
MEM_First_Name First Name MEM_Contact_No Contact Number
Agree to receive Direct Marketing?
Introduction to Information Systems
6. Now, we may redesign the Layout of the form by changing the Textbox to Option Group for the MEM_Gender field.
6.1 Under Design View: Click DesignOption Group, drag it next to the MEM_Gender Textbox
6.2 Option Group Wizard popped out, type the value as below:
6.3 Click “No, I don’t want a default.” and Next > 6.4 Click Next >
6.5 Click “Store the value in this field: MEM_Gender” and Next >
6.6 Click Option buttons and Next >
6.7 Leave the caption as defalut and click Finsih
6.8 Delete the MEm_Gender textbox, Caption and put the Option Group in the right place
Introduction to Information Systems
7. We may also change the Textbox to Combo Box for the MEM_Regions field.
7.1 Under Layout View: Click DesignCombox Box, place it next to the Living Region label.
7.2 Combo Box Wizard popped out, click “I will type in the values that I want.” and Next > 7.3 Type the values as below:
7.4 Click “Store that value in this field: MEM_Regions” and Next >
7.5 Leave the Combox Box label as default and click Finish
7.6 Delete the MEM_Regions textbox, Combox Box label and put the Combo Box as below
8. Layout of the MemberInfo Form:
Validation Rule
Validation Text
Validation Rule
Default Value
Introduction to Information Systems
Part II: Restrict the user input by Validation Rule
You can use the Validation Rule property and the Validation Text property of a form control to validate data that is input to that control and to help users who input data that is not valid.
Here is the common Validation Rule:
LIKE OR OR
Is Not Null
You must enter a positive number. Birth/Purchase date cannot be in the future. Enter M for male or F for female.
Enter a valid .com, .net, or .org e-mail address.
Please enter the value. 1. Under Layout View: Click on the Data Tab in the Property Sheet
2. Set the Default Value OR Validation Rule with meaningful Validation Text for the below field:
MEM_ID MEM_Last_Name MEM_First_Name MEM_Gender MEM_Month MEM_Contact_No MEM_Regions
Like “1#####” Is Not Null
Is Not Null
In (1,2,3,4,5,6,7,8,9,10,11,12) Like “########”
” Island”
Part III: Change the Theme OR Logo of the Form
1. Click Design Tab, change the logo, themes as you like.
2. New look of the MemberInfo Form:
Introduction to Information Systems
Part IV: Add Button for Delete Record
1. Under Layout View: Click DesignButton, place it under the MEM_DM check box.
2. Command Button Wizard popped out, choose the setting as below:
3. Choose “Text: Delete Record” and Next >
4. Name the Button as Btn_Delete and click Finsih
Part V: Add Button for Query
1. Create a query to retrieve the Member’s Favorite Product
2. Copy the below coding under the SQL view
SELECT PRO_Name, SUM(PUR_Quantity) AS Quantity
FROM MemberInfo, PurchaseRecord, ProductInfo
WHERE MEM_ID = PUR_MEM_ID AND PRO_ID = PUR_PRO_ID AND MEM_ID = [Forms]![MemberInfo]![MEM_ID]
GROUP BY PRO_Name
ORDER BY SUM(PUR_Quantity) DESC;
Word Meaning
[Forms] Form object
[MemberInfo] Name of the Form
[MEM_ID] The place that you
store the MEM_ID value on the Form
3. Save the Query name as FavProduct
4. Under Layout View of Form: Click DesignButton, place it above the Delete Record button.
5. Command Button Wizard popped out, choose the setting as below:
Introduction to Information Systems
6. Choose FavProduct and click Next >
7. Choose “Text: Run Query” and Next >
8. Name the Button as Btn_FavProduct and click Finsih
9. New look of the MemberInfo Form:
2. REPORTS
Example: Create a Report to show Monthly Purchase Record for PurchaseRecord table
1. Click Database Tools Relationships
2. Open PurchaseRecord table Click Create Report
3. Delete the following columns from the report: PUR_ID, PUR_MEM_ID
4. Click Add Existing Fields Show all tables under Field List
5. Double click PRO_Name in ProductInfo table
6. Delete PUR_PRO_ID column
7. Click Group & Sort button
8. Click Add a groupGroup on PUR_Date, by month, with PUR_Quantity [Type: SUM; Show
subtotal in group header]
Introduction to Information Systems
9. Click Add a groupGroup on PRO_Name with PUR_Quantity [Type: SUM; Show subtotal in group header]
10. Sort by PUR_Date from oldest to newest
11. Add New label to show Subtotal and Total Quantity by clicking
12. Drag the SUM of PUR_Quantity to proper place
13. Report Outcome:
This is not a Design Course. No need to remember all the steps on how to create the Forms and Reports. You just need to remember the basic concept of Forms and Reports and what we can do with them:
Change the layout of the Form
Restrict the user input by Validation Rule
Change the theme or logo of the Form
Add button for Delete Record
Add button for Run Query
Create Relationships before creating Reports Group & Sort the field shown in the Reports
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com