copy data from one table to another

[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!

 

[Rick B]

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.

 

[Joseph Meehan]

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.

 

[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.