Task DB2: Stationery supplier

In this task, you will create a database for Officeline, an office supplies company based in Edmonton, Alberta. This company supplies stationery, paper and other office consumables to companies and individuals all over Canada.

In completing this task, you will apply what you learned last week, and learn how to:

  • import data from a CSV file that contains field names
  • create queries based upon an imported data set
  • create runtime queries that perform calculations

Follow the instructions step-by-step, and expand the “Find out how” sections for more guidance if you need it.

Instructions

1. Set your computer’s regional settings to English (Canada).

2. Open Microsoft Access and create a new database called Officeline.accdb

3. Download this dataset (stationerysales.csv) and import it to your database as a new table called tbl_Orders. When importing, make sure:

  • You tick the box that says “First row contains field names
  • Set the field Row ID to be the primary key

4. Enter design view and set the appropriate data type for any fields containing dates or currency.

5. Add the following validation rules to your table:

  • Officeline only operated from the start of 2009 to the end of 2015. Add a validation rule to both date fields to reflect this.
  • Discount must be between 0 and 0.25.
  • Shipping Cost must be between $0.49 and $200.

6. In your imported data set, the province Saskatchewan has been mis-spelled as Saskachewan (with no t). Use the Replace function to fix this problem.

7. Add the three additional sales included in moresales.csv to the bottom of your table.

Note: Some people have problems importing these three records because of the format of the dates. If you do, please watch the video below for a solution.

8. Create a new table called tbl_Provinces with the following fields, all of which should be text data type:

FieldNotes
ProvincePrimary key
Sales Manager
Office Address
City
Zip CodeValidation needed, e.g. A1B 2C3 or A1A 1A1.
PhoneValidation needed, e.g. 192-555-2049.
Email

9. Import the dataset from provinces.csv to tbl_Provinces. There should be 13 records in total.

10. Create a relationship between the two tables you have made & enforce referential integrity to ensure that one province can have many sales.

Queries

11. Create a simple query called qry_AlbertaSales to display all orders from Alberta in ascending order of Ship Date. Display only fields Order IDCustomer NameProduct NameOrder Quantity and Ship Date.

12. Create a query to display all Critical orders called qry_CriticalOrders. Display only the fields OrderIDCustomer NameProduct NameUnit Sale PriceOrder QuantityDiscount and Shipping Cost.

13. Edit qry_CriticalOrders to insert a new field called Item Subtotal (between Order Quantity and Discount) that multiplies Unit Sale Price by Order Quantity. This is now a runtime query, as it will calculate the answer to this calculation when it is run.

14. Add another field to qry_CriticalOrders (between Discount and Shipping Cost) called Discount Price that multiplies Item Subtotal by Discount.

15. Add another field to qry_CriticalOrders after Shipping Cost called Total. This should calculate the following: [Item Subtotal]-[Discount Price]+[Shipping Cost].

16. Create a query called qryOrderProvince that takes all the fields from tblOrder. In the criteria for the Province field, type [Please select the province]. Run it. What does this do?

The above is called a parameter value query, and allows you to open a window where the database user can type whatever they are searching for. This can be very useful, and can also be paired with numerical operators.

17. Create a parameter value query that allows the user to type the minimum quantity of items ordered. Hint: >=[Please enter the minimum… ]

Key takeaways

What important ideas are you going to take away from this activity?

Example: Error messages when importing data tell you that you have problems with your validation rules.

Building reports

Before we can make some printable reports, we need to make our query results a little more manageable by refining our queries from earlier. If you don’t have a completed database of your own, you can download and use this one which is complete up to step 17.

18. Open qry_AlbertaSales and refine it so it only shows results from the first three months of 2012.

For all reports that follow, they should all have:

  • The Officeline logo in the top-right corner (transparent background, no cropping, at least 3cm wide)
  • The title in a 24pt sans serif bold font
  • Your name, centre number and candidate number in the bottom left of every page (not just at the end of the report)

19. Create a report for the new qry_AlbertaSales query with the following parameters:

  • Add the title “Alberta sales Jan-Mar 2012”
  • Sort the records in ascending order of ship date
  • Make the page orientation landscape
  • Ensure all fields are visible and the report prints on one page wide
  • Add an average underneath the ‘Order Quantity’ field with a label to the left of the value that says ‘Average order quantity’

20. Edit your qry_CriticalOrders query to only display records where the shipping cost is over $60. You should have 63 records.

21. Create a report for your Critical Orders query with the following parameters:

  • Shows only the fields Order ID, Customer Name, Product Name, Item Subtotal, Discount Price, Shipping Cost, Total
  • Has page orientation of landscape
  • Has the title ‘Critical orders with shipping cost over $60’
  • Has a count of records at the bottom of the report
  • Is organised in descending order of Total
Find out how