Results 1 to 11 of 11

Thread: query problem

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: query problem

    I have a table I want to query and get a sum and avg for the qtr. For example:Table 1

    (Tech) (Team) (Call Volume ) (Hrs) (Month)
    Jan Green Red 2025 145 January
    Jan Green Red 1985 138 February
    Jan Green Red 2325 132 March
    Sam Ivy Red 1689 126 January
    Sam Ivy Red 1586 141 February
    Sam Ivy Red 2366 158 March
    Bart White Green 1956 118 January
    Bart White Green 2588 156 February
    Bart White Green 2548 148 March

    I want to sum the call volume and hrs of the three months for each person and then get an avg of the three months. I started putting fields in query design like the Tech, Team Call Volumn.... fields but when I do an expression for :3month: Sum([hrs]), I get stuck. What's the best way to do this?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Code:
    SELECT Format([CallDate],"q") AS Quarter, Tech, Team, 
    Sum(CallVolume) AS SumOfCallVolume, Sum(Hrs) AS SumOfHrs, 
    Sum([CallVolume])/3 AS AvgCallVol, Sum([Hrs])/3 AS AvgHrs
    FROM tblSlimJen
    GROUP BY Format([CallDate],"q"), Tech, Team;
    Using the above SQL, here are the results:
    Code:
    Qtr  Tech  Team        SumOfCallVolume   SumOfHrs   AvgCallVol    AvgHrs
    1   Bart  WhiteGreen     7092             422       2364         140.6667
    1   Jan   GreenRed       6335             415       2111.6667    138.3333
    1   Sam   Ivy Red        5641             425       1880.3333    141.6667
    Hope this helps,

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thank you so much. I'll try this.

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Ok I tried this and it works but only without the Month field. It doesn't group together the month. If I have a year worth of months and I want only the quarter; how do I identify the quarter months I want get a sum and average on. I get the same results from the query that I have in the table. Each person is listed on a differentt line of for that month. How do I say I want the sum and avg for just Jan Feb and March with out getting three rows for each person? I want:

    Jan+Feb+Mar Tech SumofCallVolume SumofHrs AvgCallVol
    Qtr1 Bart Green 7092 422 2364

    I'm getting the same thing as in my table. Only if I leave out the month does it work. This is fine if I have only 3 months, but if I have more than three months, it won't work if I need a just a qtr.
    Thansk

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Format([CallDate],"q") AS Quarter

    The above will give you the quarter, which is why I put it in the SQL. But it sounds like you do not have a date in your table, but you have actual month names. Is that correct?

    If so, you will need to turn these month names into quarters. Therefore, you would have a very long IIf statement, or better yet, write a function that will turn a month name into the quarter that month is a part of.

    Here would be the start of the IIf statement. I will not write the whole thing, it is way too long
    Code:
    IIf(Month="Jan", 1, IIf(Month="Feb", 1, (IIf(Month="Mar",1,(IIf(Month="Apr",2,(IIF.....))))))))))))
    Where the dots are (.....) you would need to fill in the rest. Please note, the number being put in here is the quarter value. 1 for qtr1, etc. You can put anything in here you want. It could be "Qrt1" rather than just the 1.

    Or you could write a function (my choice) to do this. In the query, you would have Qtr: GetQuarter(Month) and the function would look like this:
    Code:
    Function GetQuarter(mMonth as string) as String
    Select Case mMonth
      Case "Jan", "Feb", "Mar"
         GetQuarter = "1"      'or "Qtr1" or whatever you want
      Case "Apr", "May", "Jun"
         GetQuarter = "2"      'or as above
      .
      .
      .
      End Select
    End Function
    Hope this helps,

  6. #6
    Join Date
    Jul 2004
    Posts
    214
    Ok this makes sense but do I put all of this into an expression builder? I've never written a function in a query.

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    Ok I think I figured it out. I put Qtr: GetQuarter(Month) in the query field and put the function in the criteria build. I get invalid syntax. Here is what I put in the criteria build:

    Code:
    Function GetQuarter(mMonth as string) as String
    Select Case mMonth
      Case "January", "February", "March"
         GetQuarter = "Qtr1" 
      Case "April", "May", "June"
         GetQuarter = "Qtr2"    
      Case "July", "August", "September"
         GetQuarter = "Qtr3"
    Case "October", "November", "December"
         GetQuarter = "Qtr4"
      End Select
    End Function
    Is there something I lefted out?
    Thanks

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    The function goes into a Module (NOT macro). So, at the database window, click on module, click on New, then enter what you have above. Save it, press Alt F11 to return to the database window, then try the query again, after having taken the above code out of the criteria row.

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    Ok. I created the funtion in the module. How do I link the function to the query? I put Qtr: GetQuarter(Month) into a field but got an error.

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

  11. #11
    Join Date
    Jul 2004
    Posts
    214
    Undefined funtion 'Get Quarter' in expression

Posting Permissions

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