Expert answer:Access help
Solved by verified expert:The last one I was able to do quite a bit of it, this one I am stuck on a few areas. I’d prefer to just have it all solved so that I can go about reviewing it all. it has images attached as they will be used in the small access assignment.
a03ch05condon.jpg
a03ch05liu.jpg
a03ch05matrell.jpg
a03ch05liu.pdf
yo16_ac_ch05_grader_pc_hw_instructions.docx
pack_a03ch05_grader_pc_golf.zip
Unformatted Attachment Preview
Awarded to
Ginger Liu
for successfully completing
New Employee Orientation Training
Topics included: Basic Safety & Health Rules, Security Procedures, Personal
Protective Equipment, Emergency procedures, Employee Responsibilities, Disciplinary
Policy, Training and Education, Information and Communication, Equipment, & Tool
Safety. The course was prepared to meet all applicable standards. The course
instruction included lecture, audio-visual aids, video, and a quiz.
May 1, 2018
Santa Fe, New Mexico
Barry Cheney
_________________________________________
Golf Course Manager
Office 2016 – myitlab:grader – Instructions
Your Office Access Chapter 5 Prepare Case
YO16_AC_CH05_GRADER_PC_HW – Modifying Database Tables Name
Project Description:
The Red Bluff Golf Club & Pro Shop generates revenue through its golfers, golfer services, and pro shop sales.
The current database tracking this data has erroneous data from a lack of good table design. Barry Cheney, the
Golf Club manager, has given you a copy of the database and has asked you to modify the tables used to track
employees and club members. This will make data entry easier and more consistent. To keep the file small while
you work with the database, he removed most of the data and left only some sample data. Once Barry accepts
your changes, he will load all of the data and implement the new database.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
Points
Possible
Instructions
Start Access. Open the downloaded Access file named a03ch05_grader_pc_golf.accdb. Save
the file with the name a03ch05_grader_pc_golf_LastFirst using your last and first name.
If necessary, enable the content.
0
Open the tblMember table in Design view and create an input mask for the phone number field
to make it easier to enter data. Select the appropriate setting so that the data is stored with
the symbols in the mask.
Switch to Datasheet view of the tblMember table and enter the following record to test the
changes:
2
FirstName
JoAnn
LastName
Pollack
Address
124 6th
Street
City
Spring
Hill
State
NM
ZipCode
87588
Phone
5055552010
3.5
Close tblMember.
Open the tblEmployee table in Design view and create a short date input mask for the
HireDate field.
Switch to Datasheet view of the tblEmployee table and enter the following record to test the
changes.
3
3.5
FirstName
LastName
Salary
HireDate
Position
Lilly
Baine
21375
07052018
Golf Caddy
Close tblEmployee.
Open tblMember in Design view and create a custom input mask for the State field so that
data entered is required to be 2 capital letters. Refer to the table below for specifics.
4
Description: State
Input Mask: >LL
Placeholder: _
Sample Data: NM
Mask/Type: Text/Unbound
3
Switch to Datasheet view of the tblMember table and enter the following record to test the
changes.
Updated: 11/22/2016
1
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Your Office Access Chapter 5 Prepare Case
Step
Points
Possible
Instructions
FirstName
Charlie
LastName
Edwards
Address
191 Drake
Court
City
Santa
Fe
State
NM
ZipCode
87594
Phone
5055558676
Close tbMember.
5
6
Open tblEmployee in Design view and apply a custom format to the EmployeeID field so that
all employee IDs entered appear with the letters EMP before the ID number.
Create a custom format for the Positon field, in the tblEmployee table, so that data in this field
appear with a green font color.
3
4
Apply a Medium Date format to the HireDate field, in the tblEmployee table.
Switch to Datasheet view of the tblEmployee table and enter the following records to test the
changes.
7
8
9
FirstName
Dennis
LastName
Matrell
Salary
18200
HireDate
08122018
Position
Golf Caddy
Antonio
Ruweeze
36900
08242018
Pro Shop Attendant
Switch to Design view of the tblEmployee table and modify the Validation Rule property of the
HireDate field so that when employee records are added to the table the hire date cannot be
entered after 14 days from the hire date.
Modify the Validation Text property of the HireDate field, in the tblEmployee table, to display
the following message if a hire date entered is more than 14 days from the current date: This
employee began working more than 14 days ago. Please call the Corporate Office
at (800) 555-4022. (include the period).
2
3
2
Save the changes and click No when Access prompts you to confirm whether you want to test
the existing data against the new rules.
10
11
Modify the Validation Rule property for the Salary field, in the tblEmployee table, so that no
salary can exceed $200,000.
Modify the Validation Text property of the Salary field, in the tblEmployee table, to display the
following message if an amount entered exceeds $200,000: The maximum salary an
employee can earn is $200,000. Please re-enter this employee’s salary. (include the
period).
3
3
Save the changes and click Yes when Access prompts you to confirm whether you want to test
the existing data against the new rules.
Switch to Datasheet view of the tblEmployee table and enter the following records to test the
changes to the properties.
12
FirstName
Dolly
LastName
Hunt
Salary
23600
HireDate
08252018
Allie
Madison
80000
08252018
Updated: 11/22/2016
2
Position
Golf
Technician
Golf
Professional
2
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Your Office Access Chapter 5 Prepare Case
Step
Points
Possible
Instructions
Switch to Design view of the tblEmployee table and modify the caption properties for the
following fields.
13
Field Name
FirstName
LastName
HireDate
Position
Caption
First Name
Last Name
Date of Hire
Job Title
3
Save and close tblEmployee.
Open the tblPosition table in Design view and create a single-field index using the following
information.
Index Name
Position
14
Field Name
PositionType
Sort Order
Ascending
3
Index Properties:
Primary: No
Unique: No
Ignore Nulls: No
Save and close tblPosition.
Open the tblMember table in Design view and create a multiple-field index using the following
information.
Index Name
MemberName
15
Field Name
LastName
FirstName
Sort Order
Ascending
Ascending
3
Index Properties:
Primary: No
Unique: No
Ignore Nulls: No
16
In Design view of the tblMember table, make the following fields required by setting the
Required property to Yes and the Allow Zero Length property to No: FirstName, LastName,
Address, City, State, and PostalCode.
3
Using the following information modify the appropriate property of the tblMember table so that
when new records are added data automatically appears in the fields.
17
18
Field Name
Default Value
City
Santa Fe
State
NM
Switch to Datasheet view of the tblMember table and enter the following record to test the
changes.
FirstName
Karen
Updated: 11/22/2016
LastName
Meyer
Address
27 First
Avenue
City
Santa Fe
3
State
NM
ZipCode
87594
Phone
5055552787
3
2
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Your Office Access Chapter 5 Prepare Case
Step
Points
Possible
Instructions
Harry
Shay
60258
Wildwood
Road
Snowflake
AZ
85937
9285551638
Save and close tblMember.
19
Open the tblMemberLessons table in Design view. Modify the MemberID field to make it a
lookup field that will display the MemberID, LastName, and FirstName fields, in that order,
from the tblMember table. Sort in Ascending order by LastName and then by FirstName. Be
sure to hide the key column in the field, enable data integrity, and restrict delete.
3
20
Modify the Lookup field property for the MemberID field so that column headings are visible.
Save and close tblMemberLessons.
3
21
Open the tblPosition table in Design view. Modify the PositionType field to make it a lookup
field that will display the following values: Full-time, Part-time. Be sure to limit to list and
DO NOT allow multiple values.
3
Switch to Datasheet view of the tblPosition table and enter the following information into the
PositionType field.
22
Position
Caddy Master
Golf Caddy
Golf Course Manager
Golf Technician
Head Golf Professional
Part-time Golf Professional
Pro Shop Attendant
Pro Shop Manager
PositionType
Full-time
Part-time
Full-time
Full-time
Full-time
Part-time
Part-time
Full-time
2
Save and close tblPosition.
23
Open the tblEmployee table in Design view. Create a new field named NameTag with a data
type of Calculated. In the Expression box, use the & symbol to combine the FirstName and
LastName fields separated by a space character. In the Description property type Used for
printing name tags
3
Switch to Datasheet view and enter the following records to test the changes.
24
25
FirstName
Ken
LastName
Condon
Salary
67725
HireDate
08262018
Position
Assistant Golf
Professional
Switch to Design view of the tblEmployee table. Create a new field named Orientation with a
data type of Yes/No. In the Description property, type Is new hire orientation complete?
1
3
Switch to Datasheet view of the tblEmployee table and click the Orientation check box for the
following employees to indicate that their orientation has been completed.
26
Ken Cordon
John Schilling
Robert Lange
Ginger Liu
Juan Martinez
Updated: 11/22/2016
1
4
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Step
Your Office Access Chapter 5 Prepare Case
Points
Possible
Instructions
Samual Dancer
Paul Kimons
Aleeta Herriott
Lilly Baine
Dennis Matrell
Antonio Ruweeze
Save and close tblEmployee.
Create a new table in the database that will be used to track when members have paid their
annual dues. Use the following information to create the fields in the table.
27
28
Field Name
PaymentID
Data Type
AutoNumber
Description
Field Properties
Surrogate key for each
Primary Key
payment (primary key)
PaymentDate Date/Time
Payment date
Short Date Format
AmountPaid
Currency
Payment amount
Continue creating the new table by adding a new lookup field named MemberID that uses
the MemberID, LastName, and FirstName fields, in that order, from the tblMember table. Sort
the lookup field in ascending order by LastName and then by FirstName. Hide the key column,
enable data integrity, and restrict delete.
3
3
In the Description property of the MemberID field, type This is the member who made the
payment. (include the period)
Save the table as tblPayment.
Switch to Datasheet view of the tblPayment table and enter the following records.
PaymentDate
1/26/2018
2/23/2018
AmountPaid
1200
1500
MemberID
Duke
Risotto
29
2
Delete the record where the PaymentID is 1 and enter the following record.
PaymentDate
3/5/2018
30
AmountPaid
500
MemberID
Pollack
Switch to Design view of the tblPayment table. Modify the New Values property of the
PaymentID field from Increment to Random.
3
Switch to Datasheet view of the tblPayment table and enter the following records.
31
PaymentDate
4/8/2018
5/25/2018
AmountPaid
800
2250
MemberID
Allen
Britt
2
Save and close tblPayment.
Open the tblEmployee table in Design view and add the following new fields.
32
Field Name
EmailAddress
Photo
OrientationSignOff
Updated: 11/22/2016
Data Type
Hyperlink
OLE Object
Attachment
Description
E-mail address
Golf photo
Form verifying
orientation completion
5
3
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
Office 2016 – myitlab:grader – Instructions
Your Office Access Chapter 5 Prepare Case
Step
Points
Possible
Instructions
Switch to Datasheet view of the tblEmployee table and enter the email addresses for the
following employees.
33
EmployeeID
EMP4
EMP12
EMP16
NameTag
Ginger Liu
Dennis Matrell
Ken Condon
EmailAddress
liug@paintedparadise.com
matrelld@paintedparadise.com
condonk@paintedparadise.com
2
34
In the Photo field of Ginger Liu’s record, insert the a03ch05Liu.jpg file. Be sure and check the
Link check box before closing the dialog box.
2
35
In the Photo field of Dennis Matrell’s record, insert the a03ch05Matrell.jpg file. Be sure and
check the Link check box before closing the dialog box.
2
36
In the Photo field of Ken Condon’s record, insert the a03ch05Condon.jpg file. Be sure and
check the Link check box before closing the dialog box.
2
In the OrientationSignOff field of Ginger Liu’s record, insert the a03ch05Liu.pdf file.
37
2
Save and close tblEmployee.
38
39
In Datasheet view of the tblMember table, create an advanced filter that will display only the
records where the city equals “Santa Fe” or “Cowles”. Save the filter as a query object with
the name qrySantaFeMembers.
Close qrySantaFeMembers and tblMember.
Use the Table Analyzer Wizard to reduce redundancy in the tblMember table. Let the wizard
deicide how to modify the table. The wizard will create two tables. Rename Table1 as
tblMemberData and Table2 as tblCityZip. There should be no typographical errors and
there is no need to create a query.
3
3
Close tblMemberData and tblCityZip.
40
Exit Access, and then submit your file as directed by your instructor.
0
Total Points
Updated: 11/22/2016
6
100
YO16_AC_CH05_GRADER_PC_HW_Instructions.docx
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades