Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Unanswered: Query Help: Making a distinct list with grouping and summing

    I have a table of Employee PTO data in the following format:

    Employee Date Used
    Name 1 4/1/13 -8
    Name 2 4/6/13 -4
    Name 2 4/12/13 -8
    Name 3 5/2/13 -2
    Name 4 5/6/13 -8
    Name 5 4/28/13 -4
    Name 5 4/29/13 -4
    Name 5 6/15/13 -8

    I was able to get a list of names and sum up the "Used" for each person, but i'd also like it to group it by the month, so i can see something like this:

    Name 1
    April -8
    Name 2
    April -12
    Name 3
    May -2
    Name 4
    May -8
    Name 5
    April -8
    June -8

    I think in this format i'll be able to use the queried info along with some Help Desk information to calculate the Agent Utilization percentage.

    I suppose it could also be in this format:

    Name April May
    1 -8 0
    2 -12 0
    3 0 -2
    4 0 -8
    5 -8 -8

    Here's the query i have so far:

    SELECT C.Employee, Sum(C.Used) AS Total, T2.N
    FROM (SELECT T.Employee, Count(T.Employee) AS N
    FROM
    (SELECT DISTINCT Employee, Count(*) AS N
    FROM AccrualDetail GROUP BY Employee) AS T
    GROUP BY T.Employee) AS T2 INNER JOIN AccrualDetail AS C ON T2.Employee = C.Employee
    GROUP BY C.Employee, T2.N;
    Last edited by tadams; 09-16-13 at 13:30.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't confuse getting the data you need with the presentation. Access is supposed to be a rapid IDE front end tool, not a dataabse. so the first thign to do is to get the data as you need it. then work out how to display. granted in order to get the data as required you need to have a good idea of how you want the data presented, but in thsiu case thats purely about grouping the data by employee AND month

    a word of caution:- date is a reserved word and should not be used as a column (or table) name for that matter.

    so your query should be something like:-
    Code:
    select employee, Month(datecolumn), sum(used) as TotUsed from employee
    group by employee,Month(datecolumn)
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •