Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    31

    Unanswered: can someone look at this please

    I'm trying to set up this table tbavg...it is calculated from tbmain. I've tried several ways of accomplishing this but I am not getting anywhere. I've tried attaching the stripped down version of what I am trying to accomplish. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The problem is that your query does not contain a group by clause, which is required when you select both aggregate and non-aggregate columns.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    31
    how do i add this to the sql...where do i add it before the set or after it? What does the group by actually do? Pardon the ignorance but I'm very new to access. What do you mean by aggregate and non-aggregate?
    Last edited by snake9284; 03-03-04 at 00:06.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Employee_ID | DEPT | X
    1 IT 10
    2 IT 20
    3 FINANCE 30
    4 FINANCE 40


    This query is invalid:

    Select dept_id, employee, avg(x)
    from table
    group by dept.

    The reason being, that the DBMS cannot determine the value that to insert into the Employee_id column.

    Employee_ID | DEPT | AVG(X)
    ?? IT 15

    Therefore, the required SQL statement is:

    Select dept_id, avg(x)
    from table
    group by dept;

    Every column that is not selected as an aggregate function must appear in the group by clause.

    Aggregate functions: AVG, SUM, MAX, MIN, COUNT
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jan 2004
    Posts
    31
    Is what I'm trying to do possible (add all of the info from tbmain to tbavg with one query)? Do I have to do a group by for everyone one my fields in tbavg?

    So that's why I kept getting the error message about "tbmain.[Call ID] is not part of an aggregate funtion" whenever I tried to enter the AVG function to the query?
    Last edited by snake9284; 03-03-04 at 00:18.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Please explain the purpose of using the AVG function in your query.

    Example:

    Select the average sales for each department.

    Select dept_id, avg(sales)
    from table
    group by dept_id;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jan 2004
    Posts
    31
    In tbavg the fields Q1, Q2, Q3, Q4, Q5, Q6 are all calculated from tbmain 1a, 1b, 2a, 2b, 3a, 3b, 4a, 4b, 5a, 5b, 5c, 6a, 6b, 6c.

    Q1 equal the average of 1a and 1b. Q2 2a and 2b..etc..

    I've been playing with adding the GROUP BY to my sql in the update query in the db I have posted. How would I use it along with the SET in there?

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I apologize for discussing the GROUP BY function, as it is not required.

    Insert into tableA select columnA, (valA+valB)/2 from tableB;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Jan 2004
    Posts
    31
    I think that that is what I did but for some reason the result is not coming out correctly. It's not giving the results that I would expect from taking the two values and dividing them. Did you download the attachment? If you did and you run the update query and check the results in tbavg you will see this better. Example taking (3+4)/2 gives 3 instead of 3.50, it's very strange. I can't seem to figure out why it's doing that. I've become very frustrated with it...maybe I'm overlooking something very obvious.

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    To solve the problem, change the datatypes of Q1..Q6 from Integer to Double.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Jan 2004
    Posts
    31
    I can't believe that it was that simple!!! I've been pulling my hair out over this the last two nights...I was that close. r123456...my thanks to you. What exactly is the difference between all of the number field choices when setting up the tables?

    Once again thanks a heap.

Posting Permissions

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