Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: updating table with the sum of one table

    a table called departments is having the fields :
    1- department
    2- total_employees

    I need an sql code to update the total_employees by summing the number of employees from another table (staff_main) where staff main is having the following fields :
    1- employee_name
    2- department
    3- .......
    example :
    emp_name department
    aa xx
    ab xx
    cc yy

    I need the result to sum the number of employee in department xx (2 employees) and yy (1 employee) in the filed total_employees in the table departments

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a query if you need it up to date in real time.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2011
    Posts
    42
    a query will not help update the sum of employees in the department table

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by asolyman View Post
    a query will not help update the sum of employees in the department table
    it will, if your definition of "query" includes UPDATE, INSERT, DELETE along with SELECT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by asolyman View Post
    a query will not help update the sum of employees in the department table
    Correct. That's because you should not store the sum of employees in the department table. You should use a query whenever you need that number.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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