Update Query - Update Duplicate value from Single Matching value from another table

[xxx]

"Documents" Table - List of documents & related info. All document #'s unique. No Duplicates


"Employee Training" Table - List of document / employee relationships to identify training requirements. Duplicate records with the same document #/info.

I want this Employee Training Table to update certain information based on it's "Documents" Table "parent"... if you will. So where [Employee Training].[Document #] = [Documents].[Document #] SET [Employee Training].[X],[Y], & [Z] = A,B, & C. I want it to update the document information in all records, including duplicates, and I do not want to have to provide specific criteria/parameters for every single document.

None of my Update queries have been successful thus far. Can't find a VBA example. Wondering if a FOR loop or something of the sort would achieve this result, but I'm unfamiliar.

Any advice or assistance appreciated.

 

[Beetle]

I want this Employee Training Table to update certain information based on it's "Documents" Table "parent"... if you will. So where [Employee Training].[Document #] = [Documents].[Document #] SET [Employee Training].[X],[Y], & [Z] = A,B, & C. I want it to update the document information in all records, including duplicates, and I do not want to have to provide specific criteria/parameters for every single document.

You don't really specify here, but I get the feeling that A, B & C are fields that already exist in the Documents table, and you want to take this data and insert it into the X, Y & Z fields in the Employee Training table for each related document. If so, you shouldn't, as this would be redundant. If you want to display these fields you can do so with a simple Select query combining the two tables.

Or maybe I've misunderstood.

 

[xxx]

Hm... this could work I suppose. I'd have to play with a bit, but could I get it to display for all duplicate documents, not just once?

And, yes, it sounds like you have the right idea, but understand that there are many more pieces of data to be updated than 3. It will be different for every document.

You don't really specify here, but I get the feeling that A, B & C are fields that already exist in the Documents table, and you want to take this data and insert it into the X, Y & Z fields in the Employee Training table for each related document. If so, you shouldn't, as this would be redundant. If you want to display these fields you can do so with a simple Select query combining the two tables.

Or maybe I've misunderstood.

 

[xxx]

Maybe I can try to make a better illustration of the two tables:

(1) Document Table would have something like:


----Document -- Revision -- Release Date-- ECO #

1. SOP-001 ----- B ---------- 6/12/11 ----- 0250
2. SOP-002 ----- D ---------- 5/10/10 ----- 0199
3. SOP-003 ----- A ----------12/13/10 ----- 0216
4. SOP-004 ----- F -----------4/02/11 ----- 0239
5. SOP-005 ----- B ----------- 6/01/11 ----- 0249
6. etc.......
7. SOP-252 ----- C -----------5/21/11 ------ 0233

Fields for Revision, Release Date, and ECO # are subject to periodic changes when documents are revised and rereleased. To reiterate, this table would have no duplicated documents.

(2) Training table would have something like:

1. SOP-001 ----- B ----------- 6/12/11 ------ 0250 ----- Eli
2. SOP-001 ----- B ----------- 6/12/11 ------ 0250 ----- Roger
3. SOP-001 ----- B ----------- 6/12/11 ------ 0250 ----- Tammy
4. SOP-252 ----- C ----------- 5/21/11 ------ 0233 ----- Joe
5. SOP-252 ----- C ----------- 5/21/11 ------ 0233 ----- Sharon
6. SOP-005 ----- B ----------- 6/01/11 ------ 0249 ----- Dan
7. SOP-005 ----- B ----------- 6/01/11 ------ 0249 ----- Lani
8. SOP-005 ----- B ----------- 6/01/11 ------ 0249 ----- Brad
9. SOP-005 ----- B ----------- 6/01/11 ------ 0249 ----- Chris
10. SOP-003 ----- A ----------- 12/13/10 ----- 0216 ----- Andrew
11. SOP-003 ----- A ----------- 12/13/10 ----- 0216 ----- Roger
12. etc.

Each document in this table would be paired w/ a particular employee. So there will never be a duplicate document/employee pair, but likely numerous duplicates of the same document. I want to update this table to reflect the changes made in the first table for the Rev., Release Date, ECO #, and Document Name.

That more clear?

 

[spikepl]

How about a rethink of your data structure? . You have a many-to-many relation between SOps and Employees, and each SOP can have many revisions.

tblSOPS
--------
SopID
DocumentName
IssueDate


tblSopRevisions
------------
RevisionID
SopID
RevisionName
RevisionDate

tblEmployees
-------------
EmployeeID
...

tblTraining
-----------
EmployeeID
SopID
RevisionID
TrainingCompletedDate

From the above table you can extract the data you want in your Training-table.