How to update data after normalising a database?

[OzNet]

I have inherited a database for placing job seekers into jobs. The original table has 3 fields which contain job preferences using codes which had to be looked up in a book. There are around 5000 plus records in this table.

I have created two more tables, one for the Job Preferences and the other as a look up table with all the Job names (and codes).

My goal is for the table tblJobChoices to hold the job preferences and be linked to the main table instead of using the current three fields. The other table, tblJS_JobCategories will ensure consistent data entry.

Here are the tables with relevant fields.

Main Table: tblJobSeeker
Fields: JobSeekerID (Primary key), JC1, JC2, JC3 all of which holds the Job Preference codes.

Table to hold the Job Seeker’s Job Preferences: tblJobChoices
Fields: JobCode (text), Job Seeker (number – links to tblJobSeeker:JobSeekerID) There is no primary key

Lookup table for all the Job Names and codes. tblJS_JobCategories
Fields: Job Code (text – indexed no duplicates), Job Category (text), Job_Title (text – indexed duplicates allowed) There is no primary key. There is over 600 Jobs Names in this table.

My problem: How to create a query (update or append-not sure) to populate the tblJobChoices with the data that corresponds to the tblJobSeeker.JC1, JC2 and JC3 fields.

For example, if JC1, JC2 or JC3 for a particular Job Seeker = code RS11, then I want to create a record in the tblJobChoices table so that field JobCode = “Sales Assistant” which is the Job Title for the RS11 code.

I am prepared to ignore the tblJS_JobCategories table and process each code manually – it is still better than trying to update each Job Seeker record individually, but if someone can come up with a query that will populate the tblJobChoices by using the tblJS_JobCategories data then that is even better.

[OzNet]

I have inherited a database for placing job seekers into jobs. The original table has 3 fields which contain job preferences using codes which had to be looked up in a book. There are around 5000 plus records in this table.

I have created two more tables, one for the Job Preferences and the other as a look up table with all the Job names (and codes).

My goal is for the table tblJobChoices to hold the job preferences and be linked to the main table instead of using the current three fields. The other table, tblJS_JobCategories will ensure consistent data entry.

Here are the tables with relevant fields.

Main Table: tblJobSeeker
Fields: JobSeekerID (Primary key), JC1, JC2, JC3 all of which holds the Job Preference codes.

Table to hold the Job Seeker’s Job Preferences: tblJobChoices
Fields: JobCode (text), Job Seeker (number – links to tblJobSeeker:JobSeekerID) There is no primary key

Lookup table for all the Job Names and codes. tblJS_JobCategories
Fields: Job Code (text – indexed no duplicates), Job Category (text), Job_Title (text – indexed duplicates allowed) There is no primary key. There is over 600 Jobs Names in this table.

My problem: How to create a query (update or append-not sure) to populate the tblJobChoices with the data that corresponds to the tblJobSeeker.JC1, JC2 and JC3 fields.

For example, if JC1, JC2 or JC3 for a particular Job Seeker = code RS11, then I want to create a record in the tblJobChoices table so that field JobCode = “Sales Assistant” which is the Job Title for the RS11 code.

I am prepared to ignore the tblJS_JobCategories table and process each code manually – it is still better than trying to update each Job Seeker record individually, but if someone can come up with a query that will populate the tblJobChoices by using the tblJS_JobCategories data then that is even better.

I have found a way around my problem.
I created an append query and ran it three times to append the JC1, JC2 and JC3 fields and the JobSeekerID field using a criteria "is not null" to the tblJobChoices table.

I then created an update query to update the tblJobChoices.JobCode field with the tblJS_JobCategories.Job_Title field using a criteria of tblJobChoices.Job Code = tblJS_JobCategories.Job Code.

I ended up with the result I wanted except for a few records which I think maybe due to incorrect data entry initially. I have to check that out next week.

Someone with more experience and skill may have a better solution but at least I got where I needed to go.

[NeoPa]

A large question, but from what I could see well expressed. Always hard to answer nevertheless.
Thanks for posting your solution, and that you had one. It helps for other members to see an answer if any are researching similar problems :)

[NeoPa]

Having read your question now (I was intrigued) it seems you came up with a pretty decent solution anyway. No need to improve on that one I think :)