Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

copy data from one table to another

Discussion in 'Information Technology' started by Karla V, Jul 28, 2006.

Tags:
  1. Karla V

    Karla V
    Expand Collapse
    Guest

    I have a database used to track employee suggestions. One table contains the
    employee list, the other contains their suggestions. The 2 are linked by an
    employee identification number.

    Here's the issue: the employee list will be updated weekly with department
    changes, etc. However, the suggestion needs to stay with their original
    department. So, when I query the suggestion table, I want to see the
    employee's information at the time they turned in the suggestion, not the
    current information.

    How do I "copy" the information from the employee table to the suggestion
    table when the suggestion is entered. I've created a form to enter the
    suggestions and would like to select the employee name using a combo and have
    Access automatically fill in the information from the employee table on dept
    number etc., but actually store that in corresponding fields in the
    suggestion table.

    Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Akal Takht concerned on drug addiction, copying during exams Breaking News Nov 5, 2013
    SciTech New Quantum Dot Technique Combines Best of Optical and Electron Microscopy Breaking News Jun 17, 2013
    India Copy of 48th Rreport of 'LOKPAL BILL 2011' Presented in Parliament Breaking News Dec 10, 2011
    Bhagats Sheikh Farid Copyright? History of Sikhism Nov 14, 2011
    Christianity Public library find is only surviving copy of rebel hymn book Interfaith Dialogues Jan 8, 2011

  3. Rick B

    Rick B
    Expand Collapse
    Guest

    In short, you would need to add a department field to your suggestion table.
    In your submission form, you can include that field as a control on your
    form (it can be hidden if you want). Set it to the department from the
    employee table at the time the submission is added.

    If you need specifics on how to do this (code needed) post back and we can
    give you that.

    This is fairly common in cases where you want to store the cost of an item
    at the time the transaction was done, for example. Another very common
    parallel is where you have monetary exchange rates involved. You update the
    rate in a table but you also typically store the "current" rate with the
    records.

    --
    Rick B



    "Karla V" <KarlaV@discussions.microsoft.com> wrote in message
    news:4E471FAB-F48B-43E5-A19F-A4BE3F34A7C6@microsoft.com...
    >I have a database used to track employee suggestions. One table contains
    >the
    > employee list, the other contains their suggestions. The 2 are linked by
    > an
    > employee identification number.
    >
    > Here's the issue: the employee list will be updated weekly with
    > department
    > changes, etc. However, the suggestion needs to stay with their original
    > department. So, when I query the suggestion table, I want to see the
    > employee's information at the time they turned in the suggestion, not the
    > current information.
    >
    > How do I "copy" the information from the employee table to the suggestion
    > table when the suggestion is entered. I've created a form to enter the
    > suggestions and would like to select the employee name using a combo and
    > have
    > Access automatically fill in the information from the employee table on
    > dept
    > number etc., but actually store that in corresponding fields in the
    > suggestion table.
    >
    > Thanks!
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Karla V wrote:
    > I have a database used to track employee suggestions. One table
    > contains the employee list, the other contains their suggestions.
    > The 2 are linked by an employee identification number.
    >
    > Here's the issue: the employee list will be updated weekly with
    > department changes, etc. However, the suggestion needs to stay with
    > their original department. So, when I query the suggestion table, I
    > want to see the employee's information at the time they turned in the
    > suggestion, not the current information.
    >
    > How do I "copy" the information from the employee table to the
    > suggestion table when the suggestion is entered. I've created a form
    > to enter the suggestions and would like to select the employee name
    > using a combo and have Access automatically fill in the information
    > from the employee table on dept number etc., but actually store that
    > in corresponding fields in the suggestion table.
    >
    > Thanks!


    Danger danger danger Will Robinson ...

    You have a table design error.

    You should have three tables for the information you have presented.

    Employee table

    Suggestions table

    Department table

    Each suggestion will have a department and an employee related to it.

    Each employee will have a department related to them

    You may want to consider if you want to keep some sort of history as
    well.

    --
    Joseph Meehan

    Dia duit
     
  5. Karla V

    Karla V
    Expand Collapse
    Guest

    Rick,

    Thanks for responding. I have added a department field to the suggestion
    table and on the corresponding form as well. So, I guess I'm ready for the
    code part. I am not a coder so I really appreciate you providing that...in
    simple terms. :)

    One other question...if I don't hide the control (which I don't think is
    necessary in my situation), can I "override" the dept number that is
    automatically put in there. We seem to move people around quite a bit so I
    can foresee that somebody may have already moved but we need to capture their
    previous dept. number. Not a big issue, but it MIGHT happen. The user of
    this DB knows her way around tables as well, so maybe this is a "back door"
    issue that can be dealt with as needed.

    Thanks again.




    "Rick B" wrote:

    > In short, you would need to add a department field to your suggestion table.
    > In your submission form, you can include that field as a control on your
    > form (it can be hidden if you want). Set it to the department from the
    > employee table at the time the submission is added.
    >
    > If you need specifics on how to do this (code needed) post back and we can
    > give you that.
    >
    > This is fairly common in cases where you want to store the cost of an item
    > at the time the transaction was done, for example. Another very common
    > parallel is where you have monetary exchange rates involved. You update the
    > rate in a table but you also typically store the "current" rate with the
    > records.
    >
    > --
    > Rick B
    >
    >
    >
    > "Karla V" <KarlaV@discussions.microsoft.com> wrote in message
    > news:4E471FAB-F48B-43E5-A19F-A4BE3F34A7C6@microsoft.com...
    > >I have a database used to track employee suggestions. One table contains
    > >the
    > > employee list, the other contains their suggestions. The 2 are linked by
    > > an
    > > employee identification number.
    > >
    > > Here's the issue: the employee list will be updated weekly with
    > > department
    > > changes, etc. However, the suggestion needs to stay with their original
    > > department. So, when I query the suggestion table, I want to see the
    > > employee's information at the time they turned in the suggestion, not the
    > > current information.
    > >
    > > How do I "copy" the information from the employee table to the suggestion
    > > table when the suggestion is entered. I've created a form to enter the
    > > suggestions and would like to select the employee name using a combo and
    > > have
    > > Access automatically fill in the information from the employee table on
    > > dept
    > > number etc., but actually store that in corresponding fields in the
    > > suggestion table.
    > >
    > > Thanks!

    >
    >
    >
     

Share This Page