Update query not working Please help

  • Thread starter Thread starter allie357
  • Start date Start date
A

allie357

I am trying to clean up a previously built database and normalize it.
I made a table with employee names and a primary key autonumbered
Violator_ID.
Now I wanted to run an update query to add the Violator_ID to the
Violations table. When I run the query below it says it updates zero
rows. The relationship between the tables will be one to many.


UPDATE tbl_Employees INNER JOIN tbl_Violations ON
tbl_Employees.Violator_ID = tbl_Violations.Violator_ID SET
tbl_Violations.Violator_ID = [tbl_Employees].[Violator_ID]
WITH OWNERACCESS OPTION;


Any suggestions on how to fix this?
 
allie357 said:
I am trying to clean up a previously built database and normalize it.
I made a table with employee names and a primary key autonumbered
Violator_ID.
Now I wanted to run an update query to add the Violator_ID to the
Violations table. When I run the query below it says it updates zero
rows. The relationship between the tables will be one to many.


UPDATE tbl_Employees INNER JOIN tbl_Violations ON
tbl_Employees.Violator_ID = tbl_Violations.Violator_ID SET
tbl_Violations.Violator_ID = [tbl_Employees].[Violator_ID]
WITH OWNERACCESS OPTION;


Any suggestions on how to fix this?
Click to expand...

Hi allie357,
if you have to add the Violator_ID in the Violations Table you can't do the
JOIN on the violator_ID because it's not yet set...
If you have a FirstName and a LastName field in the Employee table and in
the Violations table, then you can do your join on these fields

UPDATE tbl_Violations INNER JOIN tbl_Employees ON
tbl_Employees.FirstName = tbl_Violations.FirstName AND
tbl_Employees.LastName = tbl_Violations.LastName
SET
tbl_Violations.Violator_ID = [tbl_Employees].[Violator_ID]

bye
 
Back
Top