Update query not working Please help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

allie357 - 22 Aug 2006 14:39 GMT

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?

Reply to this Message

Cinzia - 22 Aug 2006 15:25 GMT

> 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
[quoted text clipped - 9 lines]
>
> Any suggestions on how to fix this?

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

Signature

Cinzia
---------------------------------------

| http://www.riolab.org |

--------------------------------------->

Reply to this Message