I'm doing a project for work and I need to know the best way to
approach this problem. I had originally solved this delima using an
append and an update query. I set a macro to run on an onclick
command button so that 1) the append query would copy the data from
one table to an "archived" table (on the selected employee)... then 2)
the update query would delete the data from the original table (on
selected employee). '

Now I've come to a new problem. If an employee record is already in
the archived table, I can't run an append query to archive more data
on that same employee (I believe because the employeeID field is an
indexed - no duplicates field). We are archiving past disciplines
for employees. So the archive table would have the fields: Oral1,
Oral1Date, Oral2, Oral2Date, Oral3, Oral 3Date, Wrem1, Wrem1Date,
Wrem2, Wrem2Date, Wrem3, Wrem3Date, Wwarn1, Wwarn1Date, Wwarn2,
Wwarn2Date, Wwarn3, Wwarn3Date, DML1, DML1date, DML2, DML2date, DML3,
DML3date. Each set of discipline records would include: Oral, Wrem,
Wwarn, and DML. So when we archive 1 set of discipline records for
an employee, they will show up in the Archived table with a 1 after
each name. The problem comes when we are archiving the 2nd set of
discipline records for that same employee. What I believe they want
done is that when the button is clicked to archive data that 1) it
looks to see if the #1 fields (like Oral1) already have data... and if
they do, then the new archived data needs to be stored in the fields
with the 2s (like Oral2). Then if the lookup query doesn't find any
archived discipline records for that employee, then it would just
append the data... and then run the update query to delete the data
from the main discipline table.

What is the best way to handle this? I have been working on this for
2 days and have decided I need to seek help. I can usually solve
these problems myself by finding the answers online. So I do
appreciate any help you can give! Thanks a bunch!!

On Nov 3, 11:14*pm, faraa <faraa... (AT) gmail (DOT) com> wrote:
Hi faraa,

What you propose is a repeating group: multiple instances of the same
kind of information in one tuple/record. What you need to do is,
normalize the design of your table: give every instance its own tuple/
record.

You correctly described part of the problem: "I can't run an append
query to archive more data
is not the employee, but employee plus some more attributes.

Hope this sets you in the right direction.

On Nov 7, 4:20*am, floronDBA <theo.peterbro... (AT) gmail (DOT) com> wrote:
Thank you so much! I knew there had to be a simple solution I was
overlooking. I guess I was over thinking things! Thanks again!