Open the a01c1Books file and save the database as a01c1Books_LastFirst.
b. Create a new table in Design view. Save the table as Publishers. Change the primary key from ID to PubID with a Data Type of Short Text. Add the following fields and switch to Datasheet view. Save changes to the table design when prompted.
Field Name
Data Type
PubName
Short Text
PubAddress
Short Text
PubCity
Short Text
PubState
Short Text
PubZIP
Short Text
c. Enter the following data in the Publishers table and close the table.
PubID
PubName
PubAddress
PubCity
PubState
PubZIP
BB
Bantam Books
1540 Broadway
New York
NY
10036
FS
Farrar, Straus and Giroux
12 Union Square West
New York
NY
10003
KN
Knopf
299 Park Avenue
New York
NY
10171
LB
Little, Brown and Company
1271 Avenue of the Americas
New York
NY
10020
PH
Pearson/Prentice Hall
1 Lake Street
Upper Saddle
NJ
07458
SS
Simon & Schuster
100 Front Street
Riverside
NY
08075
d. Open the Maintain Authors form.
e. Navigate to Record 7 and replace YourName with your name. f. Add a new Title: Technology in Action. The ISBN is 0-13-148905-4, the PubID is PH, the PublDate is 2015, the Price is $89.95 (just type 89.95, no $), and StockAmt is 95 units. Move to any other record to save the new record. Close the form.
g. Open the Maintain Authors form again and navigate to Record 7. The changes are there because Access works from storage, not memory. Close the form again.
Sort a Query and Apply a Filter by Selection
You need to reorder a detail query so that the results are sorted alphabetically by the publisher name.
a. Open the Publishers, Books, and Authors Query.
b. Click in any record in the PubName column and sort the field in ascending order.
c. Check to make sure that four books list you as the author.
d. Click your name in the Author’s Last Name field and filter the records to show only your books.
e. Close the query and save the changes.
View a Report
You need to examine the Publishers, Books, and Authors Report to determine if the changes you made in the Maintain Authors form appear in the report.
a. Open the Publishers, Books, and Authors Report.
b. Check to make sure that the report shows four books listing you as the author.
c. View the layout of the report in Print Preview.
d. Close the report.
Filter a Table
You need to examine the Books table to determine if the changes you made in the Maintain Authors form carried through to the related table. You also will filter the table to display books published after 2010 with fewer than 100 copies in inventory.
a. Open the Books table.
b. Use Filter by Form to create a filter that will identify all books published after 2010 with fewer than 100 items in stock.
c. Apply the filter and preview the filtered table.
d. Close the table and save the changes.
Compact and Repair a Database and Back Up a Database
Now that you are satisfied that any changes made to a form or query carry through to the table, you are ready to compact, repair, and back up your file.
a. Compact and repair your database.
b. Create a backup copy of your database, accept the default file name, and save it.
c. Exit Access. Submit based on your instructor’s directions.
Note:
For this exercise, submit both the access database file and its backup copy.
*****************************************
Create a New Database
You need to examine the data in the Excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys. Primary and foreign keys are used to form the relationships between tables.
a. Open the a02c1Donors Excel workbook.
b. Open the a02c1Plants Excel workbook.
c. Examine the data in each worksheet and identify the column that will become the primary key in an Access table. Identify the foreign keys in each table.
d. Create a new, blank database named a02c1Arbor_ LastFirst.
Create a New Table
Use the new blank table created automatically by Access to hold the donations as they are received from the donors.
a. Switch to Design view and save the table as Donations.
b. Add the remaining field names in Design view. Note: The data for this table will be added later in this exercise.
• Change ID to DonationID with the AutoNumber Data Type.
• Add DonorID (a foreign key) with the Number Data Type and a field size of Long Integer.
• Add PlantID (a foreign key) as Number Data and a field size of Long Integer.
• Enter two additional fields with an appropriate data type and field properties. Hint: You need the date of donation and the amount of donation.
c. Verify the primary key is DonationID.
d. Save the table. Close the table.
Import Data from Excel
You need to use the Import Spreadsheet Data Wizard twice to import a worksheet from each Excel workbook into Access. You need to select the worksheets, specify the primary keys, set the indexing option, and name the newly imported tables (see Figures 2.12 through 2.17).
a. Click the EXTERNAL DATA tab and click Excel in the Import & Link group.
b. Locate and select the a02c1Donors workbook.
c. Set the DonorID field Indexed option to Yes (No Duplicates).
d. Select DonorID as the primary key when prompted.
e. Accept the table name Donors.
f. Import the a02c1Plants file, set the ID field as the primary key, and then change the indexing option to Yes (No Duplicates).
g. Accept the table name Plants.
h. Open each table in Datasheet view to examine the data.
i. Change the ID field name in the Plants table to PlantID.
Create Relationships
You need to create the relationships between the tables using the Relationships window. Identify the primary key fields in each table and connect them with their foreign key counterparts in related tables. Enforce referential integrity and cascade and update related fields.
a. Open the Donors table in Design view and change the Field Size property for DonorID to Long Integer so it matches the Field Size property of DonorID in the Donations table.
b. Open the Plants table in Design view and change the Field Size property for PlantID to Long Integer so it matches the Field Size property for PlantID in the Donations table.
c. Close the open tables and open the Relationships window.
d. Add the three tables to the Relationships window using the Show Table dialog box. Close the Show Tables dialog box.
e. Drag the DonorID field in the Donors table onto the DonorID field in the Donations table. Enforce referential integrity and cascade and update related fields. Drag the PlantID field from the Plants table onto the PlantID field of the Donations table. Enforce referential integrity and check the Cascade Update Related Fields option.
f. Close the Relationships window and save your changes.
Add Sample Data to the Donations Table
Add 10 records to the Donations table.
a. Add the following records to the Donations table.
Donation ID
Donor ID
Plant ID
Date Of Donation
Amount Of Donation
10
8228
611
3/1/2015
$150
18
5448
190
3/1/2015
$15
6
4091
457
3/12/2015
$125
7
11976
205
3/14/2015
$100
1
1000
25
3/17/2015
$120
12
1444
38
3/19/2015
$50
2
1444
38
4/3/2015
$50
4
10520
49
4/12/2015
$460
5
3072
102
4/19/2015
$450
21
1204
25
4/22/2012
$120
b. Sort the Donations table by the AmountOfDonation field in descending order.
Use the Query Wizard
Use the Query Wizard to create a query of all donations greater than $100 in the Donations table. Use the following guidelines:
a. Include the DonorID and AmountOfDonation fields.
b. Name the query Donations Over 100.
c. Add criteria to include only donations of more than $100.
d. Sort by ascending AmountOfDonation.
e. Save and close the query.
Create a Query in Design View
You need to create a query that identifies the people who made a donation after April 1, 2015. The query should list the date of the donation, donor’s full name (LastName, FirstName), phone number, the amount of the donation, and name of the plant they want. Sort the query by date of donation, then by donor last name. This list will be given to the Arboretum staff so they can notify the donors that a plant is ready for pickup.
a. Click the CREATE tab and click Query Design in the Queries group.
b. Add the tables and fields necessary to produce the query as stated previously. Name the query Plant Pickup List.
c. Run and print the query from Datasheet view.
Modify a Query in Design View
a. Copy the Plant Pickup List query on the Navigation Pane and paste it using ENewsletter as the query name.
b. Open the ENewsletter query in Design view and delete the DateofDonation column.
c. Add the ENewsletter field to the design and set it to sort in Ascending order. Position the ENewsletter field on the grid so that the query sorts first by ENewsletter and then by LastName.
d. Compact and repair the database. Close Access.
e. Submit based on your instructor’s directions.
Notes:
1) At the end of Step a under Add Sample Data to the Donations Table, the Donations will appear sequentially from 1 to 10 as this field was declared as an AutoNumber in Step b under the Create a New Table section. Change the donation date of Donation ID 10 (shown in the text as Donation ID 21) from 4/22/2012 to 4/22/2015.
2) Omit the print requirement in Step c under Create a Query in Design View.
3) Submit only the access database file.
***********************************
Database File Setup
Open the food database, use Save As to make a copy of the database, and then use the new database to complete this capstone exercise. You will add yourself to the employee database.
a. Locate and open a03c1Food and save the database as a03c1Food_LastFirst.
b. Open the Employees table. Add yourself as an employee. Fill in all information, with the hire date as today. Set your Title to Technical Aide, extension to 1144, and the Reports To field to Buchanan, Steven. Leave the EmployeePicture field blank.
c. Close the Employees table.
Shipping Efficiency Query
You need to create a query to calculate the number of days between the date an order was placed and the date the order was shipped for each order. As you create the query, run the query at several intervals so you can verify that the data look correct. The result of your work will be a list of orders that took more than three weeks to ship. The salespeople will be calling each customer to see if there was any problem with their order.
a. Create a query using Query Design. From the Customers table, include the fields CompanyName, ContactName, ContactTitle, and Phone. From the Orders table, include the fields OrderID, OrderDate, and ShippedDate.
b. Run the query and examine the records. Save the query as Shipping Efficiency.
c. Add a calculated field named DaysToShip to calculate the number of days taken to fill each order. (Hint: The expression will include the OrderDate and the ShippedDate; the results will not contain negative numbers.)
d. Run the query and examine the results. Does the data in the DaysToShip field look accurate? Save the query.
e. Add criteria to limit the query results to include any order that took more than 30 days to ship.
f. Add the Quantity field from the Order Details table and the ProductName field from the Products table to the query. Sort the query by ascending OrderID. When the sales reps contact these customers, these two fields will provide useful information about the orders.
g. Switch to Datasheet view to view the final results. This list will be distributed to the sales reps so they can contact the customers. In Design view, add the caption Days to Ship to the DaysToShip field.
h. Save and close the query.
Order Summary Query
You need to create an Order Summary that will show the total amount of each order in one column and the total discount amount in another column. This query will require four tables: Orders, Order Details, Products, and Customers. Query to determine if employees are following the employee discount policy. You will group the data by employee name, count the orders, show the total dollars, and show the total discount amount. You will then determine which employees are following the company guidelines.
a. Create a query using Query Design and add the four tables above. Add the fields OrderID and OrderDate. Set both fields’ Total row to Group By.
b. Add a calculated field in the third column. Name the field ExtendedAmount. This field should multiply the number of items ordered by the price per item. This will calculate the total amount for each order. Format the calculated field as Currency and change the caption to Total Dollars. Change the Total row to Sum.
c. Add a calculated field in the fourth column. Name the field DiscountAmount. The field should multiply the number of items ordered, the price per item, and the discount field. This will calculate the total discount for each order. Format the calculated field as Currency and add a caption of Discount Amt. Change the Total row to Sum.
d. Run the query. Save the query as Order Summary. Return to Design view.
e. Add criteria to the OrderDate field so only orders made between 1/1/2016 and 12/31/2016 are displayed. Change the Total row to Where. This expression will display only orders that were created in 2016.
f. Run the query and view the results. Save and close the query.
Order Financing Query
Northwind is considering offering financing options to their customers with 5% interest, to be paid over 12 months.
a. Create a copy of the Order Summary query named Order Financing.
b. Switch to Design view of the new query and remove the DiscountAmount field.
c. Add a new field using the Expression Builder named SamplePayment. Insert the Pmt function with the following parameters:
● Use .05/12 for the rate argument (5% interest, paid monthly)
● Use the number 12 for the num_periods argument (12 months)
● Use the calculated field ExtendedAmount for the present_value
d. Change the Total row to Expression for the SamplePayment field.
e. Change the Format for the SamplePayment field to Currency.
f. Save and close the query.
Order Summary by Country Query
You need to create one additional query based on the Order Summary query you created in a previous step. This new query will enable you to analyze the orders by country.
a. Create a copy of the Order Summary query named Order Summary by Country.
b. Replace the OrderID field with the Country field in Design view of the new query.
c. Run the query and examine the summary records; there should be 21 countries listed.
d. Switch to Design view and change the sort order so that the country with the highest ExtendedAmount is first and the country with the lowest ExtendedAmount is last.
e. Run the query and verify the results.
f. Save and close the query.
g. Exit Access and submit based on your instructor’s directions.
Notes:
1) The last sentence in Step b under Database File Setup should be "Leave the Employee Picture and Notes fields blank."
2) In the Order Summary Query initial instructions, delete the last 3 sentences (“Query to determine” to “following company guidelines”).
3) In Step e under Order Summary Query, you will have to remove the check box in the Show row of the OrderDate field before running the query.
4) In Step c under Order Summary by Country Query, add the following requirement: “Specify a total line that sums the Total Dollars and Discount Amt columns.&rdquo
Place this order or similar order and get an amazing discount.
