Center.xlsx
Sheet1
MemberID
ProgramID
First
Last
Street
City
State
Zip
Phone
DateJoined
Expiration
Status
InitiationFeeWaived
1103
201
Joseph
Eckler
29 Prospect Street
Ashland
VA
23005
804-550-3050
01/16/2013
01/16/2014
Active
TRUE
1105
204
Barry
Hassan
9 Harrington Avenue
Richmond
VA
23220
804-236-6717
03/02/2013
03/02/2015
Active
FALSE
1106
202
Debbie
Ward
18 Barton Street
Richmond
VA
23219
804-674-8462
04/22/2013
10/22/2013
Active
TRUE
1110
201
Ashish
Mittal
103 Hubbard Way
Glen Allen
VA
23058
804-553-4737
04/03/2013
04/03/2014
On Hold
TRUE
1111
203
Liz
Sorrento
134 Lincoln Road
Chester
VA
23831
804-751-1270
02/10/2013
08/20/2013
Active
FALSE
1115
203
Michelle
Kim
290 Brook Mill Road
Bon Air
VA
23235
804-323-0291
05/17/2013
05/17/2015
Active
FALSE
1116
207
Robert
DeCosta
35 Washington Street
Richmond
VA
23220
804-236-4121
08/04/2013
08/04/2014
Inactive
TRUE
1117
203
Paul
Novick
302 Great Road
Ashland
VA
23005
804-550-1164
09/20/2013
09/20/2015
Active
FALSE
1120
202
Carmen
Sanchez
1294 Monument Avenue
Richmond
VA
23219
804-674-5991
10/18/2013
09/25/2014
On Hold
FALSE
1121
209
Kelly
Smith
7 Lakeview Road
Chester
VA
23831
804-751-0466
07/18/2013
07/18/2014
Active
FALSE
1125
203
Jessica
Picard
17 Briarwood Lane
Mechanicsville
VA
23111
804-559-0909
06/22/2013
12/22/2013
Active
FALSE
1126
204
Sam
Bethel
258 Elm Street
Mechanicsville
VA
23111
804-559-1750
05/19/2013
05/19/2014
Active
TRUE
1130
205
John
Ziminsky
1702 Hunter Avenue
Chester
VA
23831
804-751-4052
10/05/2013
10/05/2015
On Hold
FALSE
1131
207
Olivia
Alexander
29 Newtown Circle
Richmond
VA
23220
804-236-0642
03/11/2013
03/11/2015
Active
FALSE
1135
201
Tina
Sun
682 Hickory Way
Chester
VA
23831
804-751-9111
06/03/2013
06/03/2014
Active
FALSE
1136
212
Mary
Grant
913 Main Street
Chester
VA
23831
804-751-8772
05/01/2013
05/01/2015
Active
FALSE
1137
210
Alan
Fraser
41 Carver Hill Road
Mechanicsville
VA
23111
804-559-5885
03/20/2013
03/20/2014
Active
TRUE
1139
210
Peter
Grigas
3 Pleasant Street
Glen Allen
VA
23058
804-553-1642
06/09/2013
06/09/2015
Inactive
TRUE
1140
206
Ronald
Cunningham
52 Putnam Road
Ashland
VA
23005
804-550-7732
02/04/2013
08/04/2013
Active
FALSE
1142
207
Kye
Nguyen
456 Harringont Avenue
Richmond
VA
23220
804-236-0325
02/20/2013
02/20/2015
On Hold
FALSE
1143
206
Barry
Feinberg
73 Endicott Avenue
Chester
VA
23831
804-751-1119
10/05/2013
10/05/2014
Active
FALSE
1144
208
Colleen
Murphy
47 Winter Street
Mechanicsville
VA
23111
804-559-1651
06/09/2013
06/09/2014
Active
FALSE
1146
209
Marlene
Halpin
75 Elm Boulevard
Bon Air
VA
23235
804-323-0346
07/17/2013
07/17/2015
On Hold
FALSE
1147
210
Elijah
Slomich
108 Roosevelt Avenue
Mechanicsville
VA
23111
804-559-5060
08/02/2013
08/02/2015
On Hold
FALSE
1148
212
Todd
Wolfe
18 Parker Avenue
Richmond
VA
23219
804-674-2140
09/20/2013
09/20/2014
Active
TRUE
1150
203
Zach
Adgate
92 Apple Blossom Lane
Ashland
VA
23005
804-550-6119
10/15/2013
10/15/2014
Active
FALSE
1151
202
Isabella
Cimino
429 South Street
Ashland
VA
23005
804-550-9870
08/02/2013
08/02/2014
Active
FALSE
1153
212
Vinnie
Tumbiolo
358 East Street
Richmond
VA
23219
804-674-1434
05/19/2013
05/29/2015
Inactive
FALSE
1154
204
Jamal
Asmal
9872 Turnpike Road
Richmond
VA
23220
804-236-2194
06/07/2013
06/07/2014
Active
TRUE
1155
210
Juliette
Beaumont
35 Walnut Street
Glen Allen
VA
23058
804-553-1275
08/01/2013
08/01/2014
Active
FALSE
1156
208
Kurt
Eisler
5 Bradford Circle
Richmond
VA
23220
804-236-8961
04/17/2013
04/17/2015
On Hold
FALSE
1158
201
Pedro
Fuente
9 Mockingbird Lane
Chester
VA
23831
804-751-6333
09/05/2013
09/05/2014
Active
FALSE
1159
211
Maggie
Hadley
6 Rose Court
Ashland
VA
23005
804-550-6103
03/19/2013
03/19/2014
Active
TRUE
1160
212
Mark
Reynolds
11 Pond Lane
Mechanicsville
VA
23111
804-559-4642
06/20/2013
06/20/2015
Active
FALSE
1165
207
Shea
McKiernan
780 Flint Street
Ashland
VA
23005
804-550-0107
07/13/2013
07/13/2014
Active
TRUE
1166
203
Abigail
Turner
99 Maple Road
Richmond
VA
23219
804-674-6807
09/01/2013
09/01/2015
On Hold
TRUE
1167
204
George
Krukonis
14 Collins Drive
Richmond
VA
23219
804-674-4333
05/05/2013
05/05/2014
Active
TRUE
1169
211
Maria
Gonzalez
100 Tower Drive
Richmond
VA
23220
804-236-6337
05/19/2013
05/19/2014
Active
FALSE
Sheet2
Sheet3
CISB 15 Access Lab 2 Instructions x
CISB 15 – Microcomputer Applications
Access Lab 2: Parkhurst Health & Fitness Center
Data Files needed for this Case Problem:
Fitness. accdb ( cont. from Tutorial 1) and
Center. xlsx
Parkhurst Health & Fitness Center
Martha Parkhurst uses the Fitness database to track information about members who join the center and the program in which each member is enrolled. She asks you to help her maintain this database. Complete the following:
1. Open the Fitness database that you created in Tutorial 1 of Access. In all steps, “your name” is your actual first and last name.
2. Open the Program table in
Design view, and change the following field properties:
Program ID: Enter
Primary key for the Description, make the Program ID the primary key, and change the Field Size property to 3.
Monthly Fee: Change the Format property to
Standard.
Physical Required: Change the Data Type to Yes/ No.
3. Save and close the Program table. Click the Yes button when a message appears indicating some data might be lost.
4. Use the Import Spreadsheet Wizard to create a new table in the Fitness database. As the source of the data, specify the Center workbook (located in the Moodlerooms file folder). Select the option to import the source data into a new table in the current database, and then click the OK button.
5. Complete the Import Spreadsheet Wizard as follows:
a. Select Sheet1 as the worksheet you want to import.
b. Accept the option specifying that the first row contains column headings.
c. Accept the field options the wizard suggests, and do not skip any fields.
d. Choose Member ID as your own primary key.
e. Import the data to a table named “MemberTable_
yourName”, and do not save your import steps.
6. Open your Member table, and then delete the InitiationFeeWaived field.
7. Modify the design of your Member table so that it matches the design shown in Figure 2- 52, including the field names and their order. (
Hint: For Text fields, delete any formats specified in the Format property boxes, including deleting the @ symbols).
Figure 2-52
Field Name
Date Type
Description
Field Size
Other Properties
Member ID
Text
Primary key
4
Program ID
Text
Foreign key
3
First Name
Text
18
Last Name
Text
18
Street
Text
30
City
Text
24
State
Text
2
Zip
Text
10
Phone
Text
14
Date Joined
Date/Time
Format: Short Date
Expiration Date
Date/Time
Date when membership expires
Format: Short Date
Membership Status
Text
Active, Inactive, or On Hold
8
8. Open the Access Help window and enter
default value as the search text. Select the Help article titled “ Set default values for fields or controls,” and then select “Set a default value for a table field.” Read that section of the Help article, and then scroll down and examine the examples of default values. Set the Default Value property for the Membership Status field to “ Active” (including the quotation marks). Close the Access Help window.
9. Save the Member table. Click the Yes button when a message appears indicating some data might be lost.
10. Add the records shown in Figure 2- 53 to the Member table.
( Hint: Use the New record button on the Home tab to add a new record.)
Figure 2- 53
Member ID
Program ID
First Name
Last Name
Street
City
State
Zip
Phone
Date Joined
Expiration Date
Membership Status
1170
210
Ed
Curran
25 Fairway Drive
Bon Air
VA
23235
804-323-6824
6/3/2010
12/3/2010
Active
1172
206
Tung
Lin
40 Green Blvd
Richmond
VA
23220
804-674-0227
11/16/2010
11/16/2010
Active
11. Resize all the columns in the datasheet to their best fit, print the datasheet showing all of the records, and then save and close the table.
1. Go to the Database Tools tab, then the Database Documenter button. Under Tables, click the checkboxes next to both the Program table and the Member table, then click on the Options button. Make sure that Properties is checked under the “Include for Table” section, make sure that the second (middle) button is selected for the “Include for Fields” section, and the second (middle) button is selected for the “Include for Indexes” section. Click OK to both dialog boxes. This will create a document (design) report of your table. Using the “Capture and Print” procedure, capture this document, paste it in into Word, and save the document as
DatabaseDocumenter2-
yourName.
12. Define a one-to-many relationship between the primary Program table and the related Member table. Make sure that you create the relationship between the common field in both tables. Select the Referential Integrity option and the Cascade Updates option for this relationship.
Note: This step is IMPORTANT – without it, you will not be able to correctly create the queries required in the next tutorial.
13. Drag the sides of each table in the Relationships window so that all fields are completely visible without scrolling. Print the relationships by clicking on the Relationship Report under the Tools Group in the Design tab. Using the “Capture and Print” procedure, capture this document, paste it in into Word, and save the document as
DatabaseRelationships2-
yourName.
Save the changes to the Relationships window as you Close this window.
14. Compact and repair your Fitness database, and then close the database.
IT IS IMPERATIVE THAT YOU CLOSE YOUR DATABASE BEFORE SUBMITTING IT FOR GRADING.
15. Create a Compressed (zipped) folder containing your DATABASE and the two accompanying files. Use the Attach file option in Moodlerooms and submit the zipped folder for grading.
Attachment checklist:
Last Name, First Name CRN nnnnn Access Lab 2.zip
Last Name, First Name CRN nnnnn Fitness.accdb Access database
DatabaseDocumenter2-
yourName
DatabaseRelationships2-
yourName
CISB 15 Access Lab 2 Instructions Page 1 of 3
Mason, Clair CRN 12345 Fitness .accdb
Program ID
Program Type
Monthly Fee
Physical Required
201
Junior Full (ages 13-17)
¤ 35.00
Yes
202
Junior Limited (ages 13-17)
¤ 25.00
Yes
203
Young Adult Full (ages 18-25)
¤ 45.00
No
204
Young Adult Limited (ages 18-25)
¤ 30.00
No
205
Adult1 Full (ages 26-35)
¤ 60.00
No
206
Adult1 Limited (ages 26-35)
¤ 45.00
No
207
Adult2 Full (ages 36-50)
¤ 60.00
No
208
Adult2 Limited (ages 36-50)
¤ 45.00
No
209
Senior1 Full (ages 51-65)
¤ 50.00
Yes
210
Senior1 Limited (ages 51-65)
¤ 35.00
Yes
211
Senior2 Full (ages 66 and over)
¤ 40.00
Yes
212
Senior2 Limited (ages 66 and over)
¤ 30.00
Yes
SELECT DISTINCTROW *
FROM [ProgramTable-ClairMason];
SELECT DISTINCTROW *
FROM [ProgramTable-ClairMason];
SELECT [ProgramTable-ClairMason].[Program ID], [ProgramTable-ClairMason].[Program Type], [ProgramTable-ClairMason].[Monthly Fee], [ProgramTable-ClairMason].[Physical Required]
FROM [ProgramTable-ClairMason];