mis
zhanghv5
databasetutorial-timekastanningsalon-part2.pdf
Database Tutorial
Figure 41: Enrollment Form in Design View After Changes
Figure42: Enrollment Form in Form View
Enrollment Identificaiton Number: [MM ~==--=====:;:=:
Customer Identification Number:
Item Type: L_~_. ____ -"","",
Task 4: Enter the Customer, Item, and Enrollment Data
1. Use your newly created Customer form to enter the customer data shown in Figure 43.
2. Use your newly created Item form to enter the item data shown in Figure 44.
3. Use your newly created Enrollment form to enter the enrollment data shown in Figure 45.
327
Database Tutorial
Figure 43: Customer Data
Customer, Identification
Number Last Name: First Name Phone Number' Street Address City State Zip Code
1 Grant Milchell (999)555-1255 1010 Boulevard Rd. San Francisco CA 94111
2 Sasser Lexina (999)555-6456 210 Rushing Meadows San Francisco CA 94112
3 Rolher Elwood J999)555-6577 3001 Ripple Creek San Francisco CA 94113
4 Chen Shibo (999)555-4789 . 15712 Tanglewood Road San Francisco CA 94113
5 Elolmani Damir (999)555-3812 2121 Hyde Parke San Francisco CA 94115
6 Schoenhals Eliah (999)555-6058 1920 Pine Drive San Francisco CA 94111
7 Erbst Troy J999)555-1300 1780 Glacier Drive San Francisco CA 94112
8 Ollinger Clarissa (999)555-9351 11908 Collrane San Francisco CA 94112
9 Blochowiak Edith (999)555-0202 1223 Ridgewood San Francisco CA 94115
10 Harley Sasha (999)555-5931 10625 Brighton San Francisco CA 94115
Figure 44: Item Data
Item'Type i Description ". ,',
Item Price·
SE001 One Session $5_00
SE002 5 Sessions. $25.00
SE003 10 Sessions $50.00
SE004 15 Sessions , $75.00
SE005 20 Sessions $100.00
SP001 One Month Unlimited $35.00
SP002 Monthly Special $30.00
SP003 Loyal Customer $29.99
SP004 Referral $29.99
SP005 Yearly Enrollment $350.00
328
Database Tutorial
Figure 45: Enrollment Data
Customer Last Name . . '. Description, . , Enrollment Date
Sasser 5 Sessions 1/17/2007 Rother One Montl1 Unlimited 1/18/2007 Cllen 10 Sessions 1/15/2007 Elotmani One Session 1/18/2007 Schoenhals Loyal Custom er i/18/2007 Erbst 15 Sessions 1/18/2007 Ottinger One Montl1 Unlimited 8/15/2007 Blochowiak 10 Sessions 8/15/2007 Hal1ey 5 Sessions ' 8/20/2007
Activity 3: Query Creation
Activity 3 creates three queries. The first query, qrySingleSession, identifies how many customers have purchased a single tanning session. The second query, qrylnactive, identifies the salon's customers who are not currently enrolled. The third query, qryNewEnrollment, identifies the customers that enrolled after August -1,2007.
Task 1: Create the qrySingleSession Query
1. From the Other group located on the Createtab, click the Query Design button. See Figure 46. -
2. The Query Design view and the Show Table dialog box open. (If the Show Table dialog box is not open, click the Show Table button located in the Query Setup group.)
3. In the Show Table dialog box, double click tblEnrollment and tblltem. The field lists for both tables should now be added to the top pane of the Query Design window. Click the Close button.
4. Add the Description field from the tblltem table and the. Itype field from the tblEnrollment table to the query design grid. (You can add a field by double clicking its name.)
5. From the Show/Hide group, click the Totals button. See Figure 47.
6. In the Total row for the IType field, click the drop-down arrow and select Count from . the drop-down list. (If the drop-down arrow is notshowing, just click by the word "By"~ The drop-down arrow should now appear.)
329.
