Training Cost Management Database

As Manager of Training for your company, you are responsible for planning and scheduling training for both technical and administrative employees. You have decided to outsource the training and have negotiated the prices listed below for your training needs. To help support your training plans you will develop a database.

  1. Using the information below design and populate two tables (Pricing and Course). Define the “Technical” field type as “Yes/No” Boolean. Define the primary keys for each table and create a relationship between them. Print the tables and the Relationship report. (Value 6 points)
  2. Prepare a query that lists each course and its cost per day. (Value 2 points)
  3. Prepare a query that lists the cost per student for each class. Assume maximum capacity for each class and that you will schedule two half-day classes on the same day to take advantage of full day pricing. (Value 2 points)

Formula:Cost/Student = (Price per day * Duration) / Capacity

  1. Create a report to calculate the cost for conducting one session of each course. You will need to create a query as input to the Report Wizard that calculates the cost of each course. Subtotal course cost by Technical and Non-technical along with the Grand Total.

(Value 2 extra credit points)

Formula: Sum (in the report)

Price per Day * Duration (calculated in the query)

Pricing Table

Technical / Price Per Day / Capacity
Yes / $2,500.00 / 25
No / $2,000.00 / 38

Course Table

Course ID / Course Name / Duration / Technical
1 / ASP Programming / 3.00 / Yes
2 / XML Programming / 3.00 / Yes
3 / PHP Programming / 2.00 / Yes
4 / MS Word-Advanced / 0.50 / No
5 / MS-Excel / 0.50 / No

Since you can’t email databases as attachments you will need to print out the tables or query results for each step.Print a copy of each table, the Relationship report and all queries and reports. Put your name at the top of each sheet and bring them to class. (a-d = 7 sheets)