Update Table Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recently took over a database that I am in the process of normalizing. I
would like to update a foreign key in one table based on the primary key of
another. The tables and fields are:

tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)

What I would like to do is delete the strName field in tblTraineeInformation
and update the TraID (FK) field with the corresponding TraID (PK) in
tblTraineeList. Can this be done with a query, or is it better to update
through VBA? I have about 20k records to update in tblTraineeInformation
with about 120 records in tblTraineeList.

If I update via VBA, my idea was to pass an array, containing TraID (PK) and
strName (tblTraineeList), to a function, compare strName
(tblTraineeInformation) and strName (tblTraineeList), and update TraID (FK)
if there is a match. Would this be the best way to do it or is there a more
efficient way? Thanks.
 
If you want to perform you tasks with a query I persume that some how the
tables can be connection. If for instance you want to connect the tables by
"strName" then my suggestion would be to run the following query and then
delete the strName field from tblTraineeInformation.

UPDATE tblTraineeList tl SET tl.TraID = ti.TraID INNER JOIN
tblTraineeInformation ti ON ti.strName = tl.TraineeList
 
tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)
Click to expand...

Maybe I am being picky, but I think you would get a much better handle on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees, then
I'd call that Trainees. Are they really the same thing? Should they be
one table?

What I would like to do is delete the strName field in
tblTraineeInformation and update the TraID (FK) field with the
corresponding TraID (PK) in tblTraineeList.
Click to expand...

That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?
Can this be done with a
query, or is it better to update through VBA?
Click to expand...

Yes: in the query designer join the tables on the names fields and update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name field.

Hope that helps


Tim F
 
Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated. My SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not updated.
Any ideas?
 
The where clause is not needed

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID];


xRoachx said:
Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated. My SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not updated.
Any ideas?

Tim Ferguson said:
Maybe I am being picky, but I think you would get a much better handle on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees, then
I'd call that Trainees. Are they really the same thing? Should they be
one table?



That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?


Yes: in the query designer join the tables on the names fields and update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name field.

Hope that helps


Tim F
Click to expand...
Click to expand...
 
I'm still getting the same results. I'm going to try a few other things to
see what I can come up with. Thanks for the help.

solex said:
The where clause is not needed

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID];


xRoachx said:
Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated. My SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not updated.
Any ideas?

Tim Ferguson said:
tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)

Maybe I am being picky, but I think you would get a much better handle on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees, then
I'd call that Trainees. Are they really the same thing? Should they be
one table?


What I would like to do is delete the strName field in
tblTraineeInformation and update the TraID (FK) field with the
corresponding TraID (PK) in tblTraineeList.

That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?

Can this be done with a
query, or is it better to update through VBA?

Yes: in the query designer join the tables on the names fields and update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name field.

Hope that helps


Tim F
Click to expand...
Click to expand...
Click to expand...
 
Roach,

I did not read you rsql carefully, you are not updating anything, you are
setting the field tblCurrentTraineeGrades.Tra_ID equal to it self, here is
what I believe to be the correct SQL note the last line.

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeList].[Tra_ID];

xRoachx said:
I'm still getting the same results. I'm going to try a few other things to
see what I can come up with. Thanks for the help.

solex said:
The where clause is not needed

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID];


xRoachx said:
Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated.
Click to expand...
My
SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not updated.
Any ideas?

:



tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)

Maybe I am being picky, but I think you would get a much better
Click to expand...
handle
on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees, then
I'd call that Trainees. Are they really the same thing? Should they be
one table?


What I would like to do is delete the strName field in
tblTraineeInformation and update the TraID (FK) field with the
corresponding TraID (PK) in tblTraineeList.

That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?

Can this be done with a
query, or is it better to update through VBA?

Yes: in the query designer join the tables on the names fields and update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name field.

Hope that helps


Tim F
Click to expand...
Click to expand...
Click to expand...
 
Thanks a lot solex, that did the trick. After updating this db for the last
week everything starts to look the same.

solex said:
Roach,

I did not read you rsql carefully, you are not updating anything, you are
setting the field tblCurrentTraineeGrades.Tra_ID equal to it self, here is
what I believe to be the correct SQL note the last line.

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeList].[Tra_ID];

xRoachx said:
I'm still getting the same results. I'm going to try a few other things to
see what I can come up with. Thanks for the help.

solex said:
The where clause is not needed

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID];


Thanks for the reply. The table tblTraineeInformation contains other
information related to a training course whereas tblTraineeList only
contains
their name and deparment. I only listed the relevant fields. Anyhow, I
tried the update the info based on a query but nothing was updated. My
SQL
code is as follows:

UPDATE tblCurrentTraineeGrades INNER JOIN tblCurrentTraineeList ON
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name SET
tblCurrentTraineeGrades.Tra_ID = [tblCurrentTraineeGrades].[Tra_ID] WHERE
tblCurrentTraineeGrades.Trainee_Name = tblCurrentTraineeList.Name;

The code returns the correct number of records but
[tblCurrentTraineeGrades].[Tra_ID] remains empty. The syntax is correct
because the query ran but I cannot figure out why the data was not
updated.
Any ideas?

:



tblTraineeInformation tblTraineeList
strName strName
TraID (FK) TraID (PK)

Maybe I am being picky, but I think you would get a much better handle
on
what you are trying to do by choosing better table names. It's a good
idea to use names that describe _what_ the table represents, rather than
the job it does. If TraineeInformation is information about trainees,
then I'd call it Trainees. If the TraineeList is a list of trainees,
then
I'd call that Trainees. Are they really the same thing? Should they be
one table?


What I would like to do is delete the strName field in
tblTraineeInformation and update the TraID (FK) field with the
corresponding TraID (PK) in tblTraineeList.

That's okay as long as the two name fields are absolutely matchable. You
can't (okay, maybe you can but it would be horrid to do) match
programmatically "Smith, John" with "J.A. Smith III". On the other hand,
to all the "John Smith" records relate to the same John Smith?

Can this be done with a
query, or is it better to update through VBA?

Yes: in the query designer join the tables on the names fields and
update
the field with the other one. Remember to back up before doing anything,
after the query; then test rigorously before removing the old name
field.

Hope that helps


Tim F
Click to expand...
Click to expand...
Click to expand...
 
Back
Top