append query?? Or Update query?? or Both?? or None??

[Minal Shah]

Hi Everyone,

I have a problem. I am not even sure that I can do this.
I have the following tables

1. Employe table - with 2 feilds -- Name of employee, EmployeeID

2. Employee Information Table -- with several feilds but the key ones
of interest are -- employee ID,Employee Name, Name of supervisor,
department

3. Training - with following feilds -- EmployeeID, Operation manual (#
of the manual they are required to train on), department, name of
supervisor, Training date,

4. Manuals table - Operation Manual#, status (current, old etc)

Now every time a manual is revised a bunch of people need to be trained
on the new revised manual.

So I created an append query that appends the Training table with
Employee ID, Department and name of Supervisor from Table 1 and 2. The
rest of the feilds are blank.

Now for those records in the Table 3. --- Training that just got
appended with the append query (and where the feild "operation manual"
is blank) I want to select the manual # from the manual table and
update these appended records in the Training table with the manual #
that was selected using a search criteria.

Can this be done at all?

if so how.

I am not knowledgeable with codes or VB. But am willing to learn. Any
and all help is truly appreciated.

 

[Bob Miller]

First off, get rid of Table 1. You have the same information in Table
2.
You could put a field in Table 4 that would have the current Manual
Version # and in Table 3 a field showing the Manual version # on which
the person last trained.
A query could then be written that would compare the two and if an
employee's last version was less than the current version they would
show up.
I recommend against using append and/or update in this case.