CISB 15 – Microcomputer Applications

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

Program ID

Text

Foreign key

First Name

Text

Last Name

Text

Street

Text

City

Text

State

Text

Zip

Text

Phone

Text

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. 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

Curran

25 Fairway Drive

Bon Air

23235

804-323-6824

6/3/2010

12/3/2010

Active

1172

206

Tung

Lin

40 Green Blvd

Richmond

23220

804-674-0227

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 [ProgramTable-ClairMason].[Program ID], [ProgramTable-ClairMason].[Program Type], [ProgramTable-ClairMason].[Monthly Fee], [ProgramTable-ClairMason].[Physical Required]
FROM [ProgramTable-ClairMason];

Turn in your highest-quality paper
Get a qualified writer to help you with

“ CISB 15 – Microcomputer Applications ”

Get high-quality paper

NEW! AI matching with writer

Still stressed with your coursework?

Get quality coursework help from an expert!

Order now