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
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