Re: Working out costs per trainee

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 11/23/04 

Date: Tue, 23 Nov 2004 18:06:48 -0500

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

Garry wrote:
>
> 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
>
> >-----Original Message-----
> >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>
> >"Garry" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:7fdc01c4d156$7ab32550$a401280a@phx.gbl...
> >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
> >
> >.
> >

Relevant Pages

  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)
  • Re: Database design question
    ... Query: Built with help from query wizard ... In a nutshell, I want the user to enter a class, say 7.75G and a square foot ... say 752 and have Access calculate the total cost. ... I want the user to enter the classification and a square foot amount ...
    (microsoft.public.access.tablesdbdesign)