Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    8

    Unanswered: updating same field in multiple tables

    Hi,

    I am trying to update the same field in multiple tables using a form but so far I have not succeeded.

    I have an employee database and their targets. In some cases a few targets overlap employees.

    Table Employee_Detail
    [Employee ID]
    [Employee Name]
    [Employee Title]

    Table CEO_Target
    [Employee ID]
    [Target Description]
    [Target]

    Table CFO_Target
    [Employee ID]
    [Target Description]
    [Target]

    So and so on forth.

    Currently, i have created a table for each employee. the table lists down the targets for each employee. Since the targets overlap, i want to define a way in which i can update the overlapped targets from one location.

    for example the revenue targets for all employees are same. i do not want to go and manually enter the revenue target for each of the employee.

    i want to create a form in which i can enter the data once and it automatically udpates the revenue target field in all of the tables for each of the employee.

    Is there a way of doing this ?

    Thanks a lot.

    Regards,

    UM

  2. #2
    Join Date
    Dec 2009
    Posts
    50
    You may want to redesign your tables to consolidate them into one table.

    Table Employee_Target
    [Employee ID]
    [Employee Type]
    [Target Description]
    [Target]

    This way, all of your data is contained in one table and you can update any of the records w/ out resorting to overly complicated queries. It's not a good prcatice to have the same data in multiple tables.
    Just a sugestion.
    Regards
    Jim

  3. #3
    Join Date
    Mar 2010
    Posts
    8
    Thanks Jim for your quick reply.

    Even if I consolidate into one table, I would still have to enter the values multiple times. Is there a way to avoid having to enter data multiple times so that when I update the value in one field, it automatically updates in all other fields.

    So in this case if I have on table, I would need to have one entry for each of the employees. Example:

    Employee ID Target Description Target
    1 revenue 100 million
    2 revenue 100 million
    3 revenue 100 million

    and so on and so forth. so i would be entering the value 100 times if i have 100 employees with the same target. Am I understanding you correct?

    Thanks.

    Regards,

    UM

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    Update Employee_Target Set [desired field] = [desired value]

    This should update the desired field in all of the records in the table;

    ex:
    Update Employee_Target set Target = '200 million'

    Jim

  5. #5
    Join Date
    Dec 2009
    Posts
    50
    If you want to be specific you can add a where clause

    Update Employee_Taget set Target = '200 million'
    where Employee_Type = 'CFO'

    Jim

Posting Permissions

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