Loop Search and Update Table

[BrokenBiker]

In my db I have three tables: tbl_Employees, tbl_CourseCodes, and tbl_TrainingMain. The tbl_TrainingMain table lists an individual (from tbl_Employees) and a training item (tbl_CourseCodes) and any relevant data, i.e. date completed, remarks, etc.

In the tbl_CourseCodes, there's a field (Basic_CrsCode) to identify those courses which need to be 'loaded' against each individual in the tbl_Employees.

As it stands right now, there's no such standardization among the employees w/regards to their training items.

What I'd like to do is search tbl_TrainingMain for each employee and each course (ID'd by Basic_CrsCode). If an individual does not have the identified course listed, then add that record to the tbl_TrainingMain.

I thought about building a query, but I only know how to make a query that looks for matches. I'm basically looking for records that don't match.

I'm thinking it would require a two-part code: First search, then append; correct?

I'll take any help I can get. I can work with VBA a bit, but don't know enough to start from scratch.

-BB

 

[highandwild]

Create a table containing all combinations using a cartesian product.

To do this put the tbl_Employees and tbl_CourseCodes tables in a query and do not link them.

Add the ID from each table into the query and save the query.

Append records from this query to the tbl_TrainingMain table if they do not already exist in there already.

Let me know where you are with this.

 

[BrokenBiker]

That's essentially what I ended up doing. I had it in my head, originally, that this needed to be done in VBA. But, instead I made a series of queries.

Qry1: Lists all employees and courses w/out a join between the two tables.
Qry2: Uses the Unmatch Wizard to compare Qry1 to tbl_TrainingMain
Qry3: Appends info from Qry2 to tbl_TrainingMain

Works like a champ! And no VBA....Should this be moved to the 'Query' Forum?

 

[highandwild]

Glad it worked.

Try to always do things using queries rather than VBA if you can as it is probably quicker to set up.

When it all works I convert the SQL to a VBA friendly format and run from code.

I do so love cartesian products, so useful.