UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using An Update Query To Update A Table Where The Same Fields In A Differnt Table Has Different Values, Access 2016    
 
   
RCDAWebmaster
post Nov 1 2019, 02:31 PM
Post#1



Posts: 14
Joined: 27-August 19



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.



Go to the top of the page
 
June7
post Nov 1 2019, 05:16 PM
Post#2



Posts: 1,035
Joined: 25-January 16



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

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Jeff B.
post Nov 2 2019, 07:35 AM
Post#3


UtterAccess VIP
Posts: 10,337
Joined: 30-April 10
From: Pacific NorthWet


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?)?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
RCDAWebmaster
post Nov 5 2019, 10:55 AM
Post#4



Posts: 14
Joined: 27-August 19



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
Go to the top of the page
 
cheekybuddha
post Nov 5 2019, 12:12 PM
Post#5


UtterAccess Moderator
Posts: 11,917
Joined: 6-December 03
From: Telegraph Hill


Just reformatting this, because it is confusing in the original post:
CODE
Spreadsheet   Employee
___________   __________
              EmployeeID
FirstName     FirstName
LastName      LastName
Division      Division
Location      Location

--------------------


Regards,

David Marten
Go to the top of the page
 
June7
post Nov 5 2019, 02:11 PM
Post#6



Posts: 1,035
Joined: 25-January 16



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.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Jeff B.
post Nov 6 2019, 09:48 AM
Post#7


UtterAccess VIP
Posts: 10,337
Joined: 30-April 10
From: Pacific NorthWet


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

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 03:43 PM