Results 1 to 8 of 8

Thread: annoying prompt

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: annoying prompt

    I created a query with a sum on two field in a table. The query works fine but it prompts me before running the query. I know checked that I spelled everything correct but I'm still being prompt. Here's how I wrote the code in the query design:

    Code:
    SumofVolume: Sum([Volume])  SumofHrs: Sum([Call Hrs])
    When I run the query I get prompted for "SumofVolume" and "SumofHrs". The fields name in the table are "Volume" and "Call Hrs". Sum is in the totals field. Why am I getting prompt?

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Could we see the whole SQL statement? That would really help so we don't have to guess a bunch of things.
    Thanks,

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Ok. When I get back tomorrow.
    Thanks

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Ok. Here's the code:
    Code:
    SELECT tblEmployee.Tech, tblTeam.TeamName, Sum(tblCalls.Volume) AS SumofVolume, Sum(tblCalls.[Call Hours]) AS SumofHrs, [SumofVolume]/[SumofHrs] AS AvgTotalHrs
    FROM (tblTeam INNER JOIN tblEmployee ON tblTeam.IDX_TeamID = tblEmployee.IDX_TeamID) INNER JOIN tblCalls ON tblEmployee.IDX_EmplID = tblCalls.IDX_EmplID
    GROUP BY tblEmployee.Tech, tblTeam.TeamName, [SumofVolume]/[SumofHrs];
    I went through query design to try to find which part of the code may be failing and all the code works and I don't get the prompt until I add the code to get an avg.
    Code:
    [SumofVolume]/[SumofHrs] AS AvgTotalHrs
    This is when I get the prompt for "sumofvolume" and "sumofhrs".
    thanks

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The problem is with using the field name aliases in another expression.
    Access can't handle that. Use the expression:
    Sum(tblCalls.Volume) /Sum(tblCalls.[Call Hours])

    And take ", [SumofVolume]/[SumofHrs]" out of the group by statetment
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Slimjen
    I'm thinking that you need to put AvgTotalHrs in the Group By statement rather than "[SumofVolume]/[SumofHrs]" as you have now. I have used aliases defined in queries before to perform further calculations the way you are doing it here and it has worked.

    What I don't understand is why do you want to group on the average calculation? If what you want is for the results to be sorted by the average calculation, you could do that in the report. That's assuming you are doing all this to feed a report.

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    thanks to your suggestions; I got it to work.

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Could you please post how you got it to work. There were several suggestions and the next person coming along with this same type of question would really like to know what was the answer.
    Thanks,

Posting Permissions

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