Steps ACCESS Instructions
20. Download the project files by clicking on the next link in this module: (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.
21. 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
Note: You may see a folder instead of the two files. If so, open the folder.
22. 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.
(Note: All files that you create below should be saved into the Lastname_Firstname_PROJECT folder!)
23. Start Access. Create a Blank Database file named Lastname_Firstname_18PR_Access, so that Lastname is YOUR lastname and Firstname is YOUR firstname. Make sure to Save this database file in the Lastname_Firstname_PROJECT folder.
24. 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
|
25. Go to Datasheet View of Spokespeople table and enter the following records:
JB1 Jim Brown Football No M
JB2 James Brown Music No M
MA1 Muhammad Ali Boxing No M
MC1 Miley Cyrus Music Yes F
RG1 Ryan Gosling Acting Yes M
WS1 Will Smith Acting No M
Save and Close the Spokespeople table.
26. 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.
27. • Open the Products table
• Make sure all field widths are resized to fit column heading/data.
• Save and Close the Products table.
(Refer to the image below for Products table)
28. • 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.
(Refer to the image below for the Relationships created)
29. Create a Simple Query Using the Query Wizard:
• Create a simple query
• The query should be based on the Spokespeople table (i.e., MAKE sure and change the table under Tables/Queries to the appropriate 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 Music AND Female Query result)
30. 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. (i.e., type "Acting on Criteria line under Career, and then type "Music" right below it on the or: line)
• Run, Save and Close the query.
(Refer to the image below for Acting OR Music Query result)
31. 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 Calculated Query result)
32. 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 Totals Query result)
33. 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.
34. 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.
35. 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 Report created)
36. 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.
37. With the Products Form opened add a new record with the following data:
• 7 for Product ID,
• 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 Products Form created)
38. Open the Products table and Sort it by Category field in ascending order. Save the change.
(Refer to the image below for Products table sorted)
39. 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
• Click the OK button.The Excel file should open.
• You can now Save and Close the ACCESS DATABASE:
o Do not save the export steps and Close the wizard.
o Save Lastname_Firstname_18PR_Access.accdb file,
o Compact and Repair Database, Close the database, and
o Exit Access application.
Attachment:- access project.rar