[X]My Assistant

[RCDAWebmaster]

I have to generate an update query to update a table based upon another table.

We are doing mandatory training at my company and we need to identify which division and location an employee is affiliated with. The company that produced the online training gave us text fields to identify the division and location. Each week we get a spreadsheet from the online training company and have to import it into our database.

Spreadsheet Employee
___________ _______________
FirstName EmployeeID
LastName FirstName
Division LastName
Location Division
Location

I then have an affiliation table that links an employee to a division.

Affiliation
____________
Employee_ID
Division_ID

What I need to do is look for employees tagged to division_ID = 0 in the affiliation table and then compare what is in the employee table under division and location with what is in the spreadsheet for division and location and update the 2 fields in the employee table if the info on the spreadsheet does not match. The training is happening yearly and since the fields are text, could change year over year. This text will assist us in manually tagging an employee to the correct division since a lot of the time the text employees give us does not match correctly our divisions ans locations.

[June7]

So info in spreadsheet would be correct and employees table would not?

Join tables and run UPDATE. If data is not different then nothing changes.

What table is spreadsheet records imported into?

This post has been edited by June7: Nov 1 2019, 05:17 PM

[Jeff B.]

Do any of your employees ever change jobs (i.e., divisions)? If so, do you need/want to be able to track what training they completed in prior positions (e.g., what if Training X applies to both old and new positions?)?

[RCDAWebmaster]

The employees could change divisions, leave and new ones can get added.

The employee table will have the information that the employees supplied only if they could not be matched with a division. Every time a new spreadsheet is processed, a comparison will be done and tblPeople may get updated. This is useful as this training database is recording state mandated training which will occur on a yearly basis and people may change divisions between trainings.

After the information is entered into tblPeople a manual check will be done to affiliate employees with the correct division. Once someone is affiliated, they no longer need to be updated.

I need a query to get tblPeople.people_ID for all the folks in the spreadsheet that have divisionID=0 including what they entered.
I need a second query that gives me the people_ID for folks that have the same info in the people table and the spreadsheet.
I then need a third query to compare the full list and filter any folks from the second query. This will leave me with a list of folks along with their new spreadsheet entries that can be inserted into tblPeople.

I would then link the 3rd query with tblPeople to update tblPeople. At this point, I'm getting an error: Operation must use an updatable query.

I have yet to find a good resolution to the error.
This post has been edited by RCDAWebmaster: Nov 5 2019, 10:58 AM

[cheekybuddha]

Just reformatting this, because it is confusing in the original post:

CODE

Spreadsheet   Employee
___________   __________
              EmployeeID
FirstName     FirstName
LastName      LastName
Division      Division
Location      Location

[June7]

Can't you link spreadsheet with employees on the first and last name fields? These are the only identifiers available. Must assure that names are spelled consistently and first/last pairs are unique.

If 3rd query is not updatable, then save records to a temporary table. Use that table in UPDATE action.

[Jeff B.]

If you are using person-names for matching purposes, what happens when the company employs more than one "John Smith"?