access

Test 2: Fun with Access COMP1115 2022 Winter

Name: ________________________________________ Duration: 2 hours

Important notice:

By submitting your answer files, you are agreeing that you have completed this test on your own and you did not receive or give aid to other students in this course.

You may print out the test paper, but the distribution of the test material to others is strictly prohibited.

Save your work right away and often! Save as “Your Name_test2” (Two marks will be deducted for not naming your file properly). Submit your work via Blackboard. +++++++++++++++++++ You are the owner of Riverside Animal Hospital. You want to keep track of the vendors you work with and their products. You decide to use Access for the task. You will need to create three table in your database, but all the queries/reports/forms will be based on two tables. Part 1: Tables

1. Create a table called Client with the following field properties:

Field Name Data type Other info

ClientID Short text Primary key

FirstName Short text

LastName Short text

Phone Short text

Address Short text

City Short text

State Short text

ZIPPostal Short text

Email Short text

The data for this table can be found in a text file (Clients) in the Blackboard. Import the data to complete the table. This table will not be used in the rest of the test. [3]

2. The second table you want is on the supplier. Instead of creating one from scratch, you are going to import an existing table called “business” from the Supplier Access file found in the Blackboard. Use the external data import function to import the table and relabel it as Suppliers. [3]

3. Once imported, make sure to set the field properties as below: [2]

Field Name Data Type Description Field size Other info

SupplierID Short text Primary key 6 Caption=Supplier ID

Company Short text 50

Category Short text 15

Address Short text 35

City Short text 25

State Short text 2

Zip Short text 10

Phone Short text 14 Caption= Contact Phone

ContactFirst Short text 20 Caption=Contact First Name

ContactLast Short text 25 Caption=Contact Last Name

InitialContact Date/Time Format=Short Date Caption=Initial Contact

4. Create a new table in Design view called Product, using the table design shown below:

[3]

Field Name Data Type Description Field size Other info

ProductID Short text Primary key 5 Caption=Product ID

SupplierID Short text Foreign key 6 Caption=Supplier ID

ProductName Short text 75 Caption=Product Name

Price Currency Format=Standard Decimal place=2

TempControl Yes/No Caption=Temp Controlled?

Sterile Yes/No Caption=Sterile?

Units Number Integer Decimal Places=0 Caption=Units/case

5. Modify the Product table structure by adding a new field between the Price and

TempControl fields. Name the new field Weight (data type: Number; field size: Single; Decimal Places: 2; Caption: Weight in Lbs). Then move the Units field so that it is positioned between the Price and Weight fields. [2]

6. Enter the following records: [2]

ProductID SupplierID Product Name

Price Units/case Weight in Lbs

Temp controlled?

Sterile?

PT100 KLS321 Paper tape roll

20.00 12 3 No No

TC050 QLS002 Thermometer covers

27.00 50 1 No Yes

CT200 KLS321 Cloth tape roll

24.00 10 4 No No

7. Use the Import Spreadsheet Wizard to add the rest of data to the Product table. The data can be found in the Blackboard (Products). [3]

8. Define a one-to-many relationship between the Supplier table and the related Product table. Select the referential integrity option and the cascade updates options for the relationship. Note: there is no link to the Client table. [3]

9. Open the Supplier table in Datasheet view and then change the following field values for the record with the supplier ID GGF099: Change Contact First Name to your first name and Contact Last Name to your last name. Close the table. [2]

Part 2: Queries Read each instruction carefully. Study the Product and Supplier tables carefully to see what fields are included in each table.

10. Create a query based on the Supplier table. Include the following fields in the query in the order shown: Company, Category, ContactFirst, ContactLast, Phone, and City. Sort the query in ascending order based on the Category field value. Save the query as ContactList and run the query. [2]

11. Use the ContactList query database to update the Supplier table by changing the phone field value for A+ Labs to 402-730-8857. [1]

12. Change the alternate row colour in the ContactList query datasheet and save the query and close. You can select any colour you want. [1]

13. Create a query based on the Supplier and Product tables. Select the Company, category and State field from the supplier table and the ProductName, Price, Units and Weight fields from the Product table. Sort the query results in descending order based on price. Select only those records with a category field value of supplies (which is a field in the supplier table), but do not display the category field value in the query results. Make sure this field is moved to the end. Save the query as SupplyProducts, run the query and close. [3]

14. Create a query that lists all products that cost more than $50 and are temperature controlled. Display the following fields from the Product table in the query results: ProductID, ProductName, Price, Units, and Sterile. Save the query as HighPriceAndTempControl. Run and close the query. [3]

15. Create a query that lists information about suppliers who sell equipment or sterile products. Include the Company, Category, ContactFirst and ContactLast fields from the Supplier table; and the ProductName, Price, TempControl and Sterile fields from the Product table. Save the query as EquipmentOrSterile, run the query and the close it. [3]

16. Create a query that lists all resale products, along with a 10% markup amount based on the price of the product. Remember resale is a category found in the supplier table. Include the Company field from the Supplier table and the following fields from the Product table in the query: ProductID, ProductName, and Price. Save the query as ResaleProductsWithMarkup. Display the markup in a calculated field named Markup that determines a 10% markup based on the Price field value. We are just interested in getting the 10% figure here. Set the Caption property Markup for the calculated field. (Hint: You can use the Property Sheet, which is found on the ribbon, for this.) Display the query results in descending order by Price. Save and run the query. [3]

17. Verify the format of the Markup field in the ResaleProductsWithMarkup query so that it uses the standard format and two decimal places. Run the query, resize all columns in the datasheet to their best fit, and then save and close the query. [2]

18. Create a query that calculates the lowest, highest, and average prices for all products using the filed names MinimumPrice, MaximumPrice and AveragePrice, respectively. Set the caption property for each field to include a space between the two words in the field name. Run the query, resize all columns and save the query as PriceStatistics and close it. [3]

19. Create another query similar to the one above. This time you are interested in the summary statistics of the same (ie., price) based on each company. [3] Save the query as PriceStatisticsByCompany. Part 3: Forms and reports

20. Use the Form Wizard to create a form based on the Product table. Select all fields for the form and the Columnar layout; specify the title as ProductData for the form. [3]

21. Apply the Ion theme to the ProductData form only. [1] 22. Insert a logo in the ProductData form. The logo can be anything related to a vet clinic or

pet from the internet. Remove the picture from the control layout, and then move the picture to the right of the form title. [2]

23. Edit the form title so that it appears as “Product Data” (two words), resize the title box as necessary so the title appears on a single line, and then change the font colour of the form title to the Light Grey, Background 2, Darker 75% theme colour. [1]

24. Use the Form Wizard to create a form containing a main form and a subform. Select all fields from the Supplier table for the main form, and select ProductID, ProductName, Price, TempControl, and Sterile – in that order- from the Product table for the subform. Use the Datasheet layout. Specify the title SuppliersAndProduct for the main form and ProductSubform for the subform. Resize the subform in the main form so that all fields are clearly visible. Including the Product, there should be 12 fields. Make sure they are lined up nicely vertically. Change the theme to Gallery for this form only. [5]

25. Use the Report Wizard to create a report based on the primary Supplier table and the related Product table. Select the SupplierID, Company, City, Category, ContactFirst,

ContactLast, and Phone fields – in that order- from the Supplier table and the ProductID, ProductName, Price, and Units fields from the Product table. Do not specify any additional grouping levels, and sort the detail records in ascending order by ProductID. Choose the Outline layout and Portrait orientation. Specify the title ProductsBySupplier for the report. [3]

26. Apply conditional formatting so that the Category filed values equal to Supplies appear as dark red and bold. [2]

27. Compact and repair the database and submit it via Blackboard. [2]

*Double check to make sure that you have uploaded the correct file. Uploading incorrect or unreadable file would result in a grade of zero.