Working out costs per trainee

G

Garry

I have 3 main tables: tblStaff, tblStaffCoursesM2M,
tblRequirementsAndCosts. Each table is linked by field
ScheduleID.
My problem is that I am trying to work out how much it has
cost to train each member of staff but 2 conditions must
be applied first.

1 tblRequirementsAndCosts stores costs of each course
that have taken place to date (Currently 78 courses). For
example, 10 staff attended MS Access so 'cost per
attendee' is £100.
2 tblStaffCoursesM2M is a M2M table that stores all
staffs' attendances training details (currently 380
attendances of all staff on all courses to date). Given
that staff attend on average 3 courses or more then the
same process in 1 needs applied but needs to be summed to
produce overall totals for each staff.

Here is sample SQLthat may be of use.

SELECT tblStaff.StaffID, tblStaffCoursesM2M.ScheduleID,
tblStaff.Forename, tblStaff.Surname,
tblRequirementsAndCosts.Cost
FROM tblRequirementsAndCosts RIGHT JOIN (tblStaff RIGHT
JOIN tblStaffCoursesM2M ON tblStaff.StaffID =
tblStaffCoursesM2M.StaffID) ON
tblRequirementsAndCosts.ScheduleID =
tblStaffCoursesM2M.ScheduleID
ORDER BY tblStaff.Surname;

What is the best way to work out 'costs per trainee' of
staff who have attended training courses.

Hope the above all makes sense - I am trying to keep it
simple and brief.

Garry
 
J

Jeff Boyce

Garry

You have a table that lists all staff training (?your M2M).

If you created a query between that table and the Course (Requirements/Cost)
table, you could list all the costs for all the courses taken by all staff.

If I understand your request, you want an average of the cost for all
courses taken by all staff -- that sounds like using the "Totals" type query
and selecting the "Average" for GroupBy.

Or have I missed something...

--
Good luck

Jeff Boyce
<Access MVP>
I have 3 main tables: tblStaff, tblStaffCoursesM2M,
tblRequirementsAndCosts. Each table is linked by field
ScheduleID.
My problem is that I am trying to work out how much it has
cost to train each member of staff but 2 conditions must
be applied first.

1 tblRequirementsAndCosts stores costs of each course
that have taken place to date (Currently 78 courses). For
example, 10 staff attended MS Access so 'cost per
attendee' is £100.
2 tblStaffCoursesM2M is a M2M table that stores all
staffs' attendances training details (currently 380
attendances of all staff on all courses to date). Given
that staff attend on average 3 courses or more then the
same process in 1 needs applied but needs to be summed to
produce overall totals for each staff.

Here is sample SQLthat may be of use.

SELECT tblStaff.StaffID, tblStaffCoursesM2M.ScheduleID,
tblStaff.Forename, tblStaff.Surname,
tblRequirementsAndCosts.Cost
FROM tblRequirementsAndCosts RIGHT JOIN (tblStaff RIGHT
JOIN tblStaffCoursesM2M ON tblStaff.StaffID =
tblStaffCoursesM2M.StaffID) ON
tblRequirementsAndCosts.ScheduleID =
tblStaffCoursesM2M.ScheduleID
ORDER BY tblStaff.Surname;

What is the best way to work out 'costs per trainee' of
staff who have attended training courses.

Hope the above all makes sense - I am trying to keep it
simple and brief.

Garry
 
G

Guest

Jeff,

Thank you for your reply.

You have a table that lists all staff training (?your M2M).
 
G

Garry

Jeff,

Thank you for your reply.
My apologies for writing the original post so
confusingly. I will try to clarify as much as I can.

Yes, my M2M does list all staff training details for all
courses. I had already done what you had suggested in
creating a query between my M2M and
tblRequirementsAndCosts (that was the sample SQL that I
had provided in my original post). The query produces a
full list of staff and cost but the problem is that for
each staff displayed it shows the total cost of the course
rather than the cost per trainees.

For example, where ScheduleID = 9 (MS Access course) 24
attendees are displayed with the cost field showing as
£3500 against each name, where it should in fact show
3500/24 = £145.82.

I'm okay with total and averages queries etc but it's
working out a query which can calculate cost per attendee
is getting me stuck.

Hope this helps

Garry
 
J

John Spencer (MVP)

The following query should give you the cost per employee for each course. You
can use it as a table in another query to get the cost of each individual's training.

SELECT RC.ScheduleID, RC.Cost/Count(SC.StaffID) as AvgCost
FROM tblRequirementsAndCosts as RC INNER JOIN
tblStaffCoursesM2M as SC ON
RC.ScheduleID = SC.ScheduleID
GROUP BY RC.ScheduleID
 
G

Garry

John,

Thank you for your reply.

Although I couldn't get your query to work (the error
message said "You tried to execute a query that does not
include the specified expression 'RC.Cost/Count
(SC.StaffID)' as part of an aggregrate function), I used
your logic and solved my problem in a different way.
Firstly, I created a total query to count ScheduleIDs and
added this query to another query to show
Cost/CountofScheduleID. It produces the correct answer
(an average cost per trainee). Maybe not the most elegant
solution but at least it works.

Thanks for your inspiration

Regards

Garry
 
Top