Updating primary keys

[Guest]

I have an database I use to summarize time cards for my company. This
summary is then sent to to our payroll processors ( another company). I use
the employee id as a primary key in the following tables: Employee, Time
Card, Stat Pay. Unfortunately the other company sets the employee ids so
when I create a new employee I make up a temporary ID. Within a week I
recieve the proper employee ID.

I have referential inegrity enforced but did not check off any of the
cascade options

Is it possible for one query to update the employee ID's on all three tables
with out the referential integrity complaining that the record cannot be
changed because other talbe reference the number? Right now my only option
is to delete and recreate my database relationships.

Thanks in advance for any help

 

[MGFoster]

I have an database I use to summarize time cards for my company. This
summary is then sent to to our payroll processors ( another company). I use
the employee id as a primary key in the following tables: Employee, Time
Card, Stat Pay. Unfortunately the other company sets the employee ids so
when I create a new employee I make up a temporary ID. Within a week I
recieve the proper employee ID.

I have referential inegrity enforced but did not check off any of the
cascade options

Is it possible for one query to update the employee ID's on all three tables
with out the referential integrity complaining that the record cannot be
changed because other talbe reference the number? Right now my only option
is to delete and recreate my database relationships.

Just set the Cascade Update option on the relationship join line. Then
change the employeeID.

 

[John Vinson]

I have an database I use to summarize time cards for my company. This
summary is then sent to to our payroll processors ( another company). I use
the employee id as a primary key in the following tables: Employee,

that's ok...

Time Card

So each employee has one and only one Time Card record, ever? What do
you do, fire them after the first paycheck? <g>

EmployeeID should be a foreign key in the related tables - NOT a
primary key!

, Stat Pay. Unfortunately the other company sets the employee ids so
when I create a new employee I make up a temporary ID. Within a week I
recieve the proper employee ID.

What's the datatype of your ID?

I have referential inegrity enforced but did not check off any of the
cascade options

Is it possible for one query to update the employee ID's on all three tables
with out the referential integrity complaining that the record cannot be
changed because other talbe reference the number? Right now my only option
is to delete and recreate my database relationships.

Do so; this time check the Cascade Updates checkbox. I normally avoid
this feature but it sounds like for this case it's appropriate.

John W. Vinson[MVP]