Microsoft Access 4

1. Microsoft Access 4 Database Creation and Management

2. Forms • Forms are used to customize your data entry screen. • Forms are created for the convenience of the user. • Forms are used to update Data Tables. • You can change locations of fields and colors very easily in the Design screen.

3. Creating a Form • After Kim leaves for her staff meeting, Barbara asks you to create a formfor the Customer table because the staff are not allowed to access the database directly. Thestaff members should be allowed only for entering data. But, using a form, the staff can work data easily in the table. • Open Customer table to identify # of records (38) • Create using “Form Wizard” • Layout: Columnar & Style: International – it’s up to you for your midterm. • Save the form as Test Form

4. Adding a Record into the Form • Add following information as a Record No.39 customer; • CustomerNum: 000 • CustomerName: Your Last Name • Street, City, State, and ZipCode: Your Address • OwnerName: Your Full Name • Phone: Your Phone Number • FirstContact: 09/20/2001 • A record No. 39 added to the Customer table (it’s has been altered).

5. Modifying a Form • Modify the Test form based on below requirements using the Design View of Form • Make sure that City, State, and Zip Code are on the same line • Title of field: Label box • Actual entry space: text box • In order to move both boxes together, place the cursor between LABEL and TEXT boxes. Then, the shape of cursor will be changed to a black colored hand.

6. Modifying a Form (con’t) • Put a space between words for followings: • CustomerName, OwnerName, and FirstContact • Change CustomerNum to Customer No • Change Phone to Phone Number • Add a label in the Form Header section in order to create a title of this form. The title (use label button from the Toolbox)for the form should be Customer Data Entry Form. • The font size of the title should be a minimum of 20 and the title must be centered.

7. Forms Practice • Create a form for the Membership Table from the Roadrunner database. Begin your form with the Wizard and then modify it so that: • Member Number is on the top line • Last Name and First Name is on the same line • Address is on a line by itself • City and State are on the same line • Class Number and Gender are on the same line • The labels for Member Number, Last Name, Address, City, and Class Number should all be lined up vertically with the same left border/margin

8. Forms Practice (con’t) • The labels for First Name, State, and Gender should all line up vertically with the same left border/margin. • You must also space out your fields and rows so that there is plenty of space around each field. • They should not be crowded together. • You should also add a label to your form in the Form Header section. The label (use label button)for your form should be Membership Data Entry Form. • The font size should be a minimum of 16 point font. • Your title must also be centered.

9. Forms Practice (con’t) • You will need to change the font color of your label text and the fill color for your label text so that your form label is easily viewable on your computer screen, given the style color/pattern that you selected for your background. • Save as Membership Data Entry Form.

10. Reports • Reports are used to “Dress Up” or summarize your data. • Reports can be made from a Table or Query. • You can use a Wizard to create your reports or you can customize them to fit your needs. • You can specify sorting orders and you can also group data.

11. Creating a Report • Kim returns from her staff meeting with another request. • She wants to have a list of OrderNum, CustomerNum, Paid and InvoiceAmt. from the Order table. She’d like the information presented in a more readable format for senior managers. • Create using “Report Wizard” • Layout: Tabular and others - it’s up to you for your midterm. • Group by: CustomerNum • Sort the InvoiceAmt in descending order • Save the report as Order List

12. Modifying a Report • Insert CSUB logo upper-right corner of the report • The title is Order List report. Title requirements; • The title must be centered. Change background and font color. Change font size to 24. • Put a space between words for followings: • OrderNum • CustomerNum • InvoiceAmt

13. Modifying a Report (con’t) • Change order Num to Order No • Change Customer Num to CustomerNo • Change InvoiceAmt to Invoice Amount • Widen out the heading names and field entries so that nothing is cut off. Space out your data fields as well.  Also, adjust vertical alignment of each field.All words in the report must be clearly visible.

14. Report Practice • We will make a report that combines elements of the Instructor Table with elements from the Course Table. • From the Instructor Table, your report will include Employee Number and Instructor Last Name. From the Course Table, your report will include Class Number, Course Name, and Time. • You need to group by Employee Number. Class Number should be sorted in ascending order. The report is called Teaching Assignments.

15. Report Practice (con’t) • After you create the basic form of the report using the Wizard, we will next need to go into the design screen and customize the report. Center the title in the middle of the page. • Change Employee_Number to Employee No. • Change Instructor_Last to Instructor. • Change Class_Number to Class. • Change Course_name to Course • Widen out the heading names and field entries so that nothing is cut off. Space out your data fields.  All words in the report must be clearly visible.

16. Creating Web-Enabled Information • Barbara feels that other employees in the company would benefit from gaining access to the Restaurant Database. Leonard asks whether the database can be made available to employees over the company network (I.e., Intranet). That way, employees could obtain company information using their desktop computers rather than using paper forms. Most employees, such as the customer representatives in the marketing department, do not need to access to the entire database, nor should they be able to make changes to the database objects.

17. Creating a Static Web Page • Leonard has asked you to create an HTML document for the Customer table. He wants this data to be available to customer representatives working outside the office. Leonard wants you to create static Web pages because the customer representatives need to view them only once a month to complete their monthly status reports. • Select the Customer table from Restaurant Database • File name: Customer • Open the table using any Internet Browser

18. Creating an Static Web Page • Easy to create and maintain • Stored outside the database • Simple to publish • Only reflect the state of the Customer table at the time it was created. • Does not allow continuous update because it’s not linked to the Customer table on which it is based.

19. Importing an Html Document as an Access Table • Download and then open NewRest HTML document. • Close NewRest HTML document. • Import NewRest HTML document into Restaurant database. • Let the Access choose the primary key. • Save as Potential Customers

20. Importing and Exporting an XML Document • Download Loans XMLdocument. • Universal way to transmit data because of the Internet • Import Loans XMLdocument into Restaurant database. • Save as Loans XML • Export Customer table as an XML document