create powerpoint, and working on access, word, and excel
hhhhh
18pr_access_project_sample_solution.pdf
Page 1 of 7
(18PR) ACCESS Project Sample Solution
Last Changed: 8/17/2016 9:45 PM
Refer to Steps 6, 8, 13, 16, 18 and 19 for sample solution images
Steps Instructions Points
1
CLICK HERE to download (18PR) Project Part II DATA.zip folder.
Note: Most Windows versions will automatically download the file to your Downloads folder. Other Windows versions may
require you to designate where you want the file saved.
0
2
Extract the data from the downloaded zipped folder. This will create a folder named: (18PR) Project Part II DATA.
You should see two files within this folder:
File4Import.xlsx and
Logo2Copy.wmf
0
3
Copy the two files listed above, that you just extracted, into your Lastname_Firstname_PROJECT folder.
Note: Recall that you created the Lastname_Firstname_PROJECT folder earlier when you completed the PowerPoint part
of the project. For more information, see (18PR) PPT Project Instructions.
(Note: All files that you create below should be saved into the
Lastname_Firstname_PROJECT folder!!!)
0
ACCESS
View the next 3 links within this module for a PDF sample solution of how your output should look after completing this exercise!
4
Start Access. Create a Blank Database file named Lastname_Firstname_18PR_Access, so that Lastname is
YOUR lastname and Firstname is YOUR firstname and make sure to Save this database file in the
Lastname_Firstname_PROJECT folder.
1
5
Close the default Table1 created in the new database file.
Create a new table using the Table Design button. Use the design information below. Name the table: Spokespeople
Field Name Data Type Field Properties
Spokesperson Short Text Field Size: 3
Required: Yes
Set this field as the PRIMARY KEY
Firstname Short Text Field Size: 15
Lastname Short Text Field Size: 25
Career Short Text
Available Yes/No
Gender Short Text Field Size: 1
5
6
Go to Datasheet View of Spokespeople table and enter the following records:
JB1 Jim Brown Football No M
JB2 James Brown Music No M
MC1 Miley Cyrus Music Yes F
MA1 Muhammad Ali Boxing No M
RG1 Ryan Gosling Acting Yes M
2
Page 2 of 7
Steps Instructions Points
WS1 Will Smith Acting No M
Save and Close the Spokespeople table.
(Refer to the image below for Spokespeople table)
7
Using Import Excel spreadsheet wizard Import data to create a new Products table. Make sure you choose the
following options in the wizard steps:
Browse to select File4Import.xlsx file located in Lastname_Firstname_PROJECT folder,
Select the option for Import the Source Data into a new table in the current database,
Make sure First Row Contains Column Heading is selected and click Next TWO times,
Make sure to Choose my own primary key as Product ID and click Next,
Finish the import to Products table,
In the final step Do not save the import steps and Close the wizard.
3
8
Open the Products table and make sure all field widths are resized to fit column heading/data. Save and Close the
Products table.
(Refer to the image below for Spokespeople table)
1
9
Create a one-to-many relationship between the Spokesperson field in the Spokespeople table and the
Spokesperson field in the Products table.
Enforce referential integrity between the two tables.
Close the Relationships window, saving the changes.
1
Page 3 of 7
Steps Instructions Points
(Refer to the image below for Spokespeople table)
10
Create a Simple Query Using the Query Wizard:
Create a simple query
The query should be based on the Spokespeople table.
Include the spokesperson's Firstname, Lastname, Career, and Gender fields, in that order.
Save the query as Music AND Female Query.
In Design view, set the Criteria to select only those records with whose Career is in Music AND whose Gender
is a Female (F).
The Gender field should not appear in the query results (make sure the Show check box is not checked for the
Gender field).
Run, Save and Close the query.
(Refer to the image below for Spokespeople table)
2
11
Create another simple query based on the Spokespeople table.
Include the spokesperson's Firstname, Lastname, and Career fields in the query.
Save the query as Acting OR Music Query.
In Design view, set the Criteria to select those records with whose Career is in Acting OR Music.
Run, Save and Close the query.
(Refer to the image below for Spokespeople table)
2
Page 4 of 7
Steps Instructions Points
12
Create another simple query based on the Products table.
Include Product Name, and Unit Price fields in the query.
Choose Detail query, and then Save the query as Calculated Query.
In Design view of the query, create a calculated field called Discount that determines the discount by
multiplying the Unit Price by .08.
Run and Save the query.
Modify the format of the calculated field to Currency, with 2 decimal places.
Display the results in descending order by Discount.
Run, Save and Close the query.
(Refer to the image below for Spokespeople table)
2
13
Create another simple query based on Spokespeople table and Products table.
Include Career field from the Spokespeople table, and include Unit Price and Unit Cost fields from the
Products table.
Choose Detail query, and then Save the query as Totals Query.
In Design view of the query, click the Totals button.
Make sure that the Career field is set to Group By in the Totals row.
Set the Unit Price field to Sum in the Totals row,
and in the Totals row, set the Unit Cost field to Avg.
Run the query.
Resize the fields to show all of the contents (headings/data).
Save and Close the query.
(Refer to the image below for Spokespeople table)
2
Page 5 of 7
Steps Instructions Points
14
Using the Report Wizard, create a new report based on the Spokespeople table.
Include all fields from Spokespeople table.
Group by Career.
Sort the records by Firstname in ascending order.
Use Stepped layout,
Landscape orientation,
and adjust field width so that they fit on one page.
Save the report as Spokespeople Report.
2
15
Change the title of the report to Your Name Spokespeople Report (Where Your Name is your actual Firstname
Lastname, ex: Mary Smith Spokespeople Report).
Also, change the report title’s font to Tahoma,
font color to Purple and
font size to 28.
2
16
Insert the Logo2Copy.wmf file (located in Lastname_Firstname_PROJECT folder) as a Logo into the
Spokespeople Report.
Resize the logo to 1” width and 1” height.
Move the report title just towards the right of the logo so that they are not overlapping with each other (if you
have trouble doing this in Layout View, try Design View)
Save and Close the report.
(Refer to the image below for Spokespeople table)
2
17 Using the Form Wizard, create a new form based on the Products table. Include all fields from Products table. Use
Columnar layout. Save the form as Products Form. 2
18 With the Products Form opened add a new record with the following data:
7 for Product ID,
2
Page 6 of 7
Steps Instructions Points
Your Fullname for Product Name,
BCIS for Category,
1405 for Unit Price,
11 for Unit Cost, and
JB2 for Spokesperson.
Save and Close the Products Form.
(Refer to the image below for Spokespeople table)
19
Open the Products table and Sort it by Category field in ascending order. Save the change.
(Refer to the image below for Spokespeople table)
1
20
Using Export to Excel spreadsheet wizard, Export data from the Products table into
Lastname_Firstname_18PR_Excel file. Make sure you choose the following options in the wizard steps:
Browse to select Lastname_Firstname_PROJECT folder,
Enter Lastname_Firstname_18PR_Excel as the destination file name so that Lastname is YOUR lastname
and Firstname is YOUR firstname,
Make sure the file format is set to *.xlsx,
Select the option for Export data with formatting and layout,
Select the option for Open the destination file…, and
3
Page 7 of 7
Steps Instructions Points
Click the OK button.
The Excel file should open. You can now Save and Close the ACCESS DATABASE
