Problem with an Update Query

[Steve]

I have a volunteer table and a uniform table, they share a primary key of
id#. Unfortunately, when I built the uniform table and populated from the
volunteer table, I had the id# field set as an auto number so the number in
the uniform table is incorrect for some rows.

I'm trying to use an update query to update the id# in the uniform table if
the id# <> the id# from the volunteer table, but I can't get it to work.
Here's what I've tried:

UPDATE UNIFORMS
SET UNIFORMS.ID = VOLUNTEER.ID
WHERE UNIFORM.ID <> VOLUNTEERS.ID

Any help would be greatly appreciated.

THANKS!

 

[John Spencer]

Did you change the Uniform.ID field from Autonumber to Number type Long?

Even if you did, there is no way to update the number since you have no
reliable way to say this Uniform.ID is 22 and it should be 19 because
Volunteer 19 was the one that got this uniform. You will have to
manually edit the records.

Volunteers wear Uniforms so you should have a table structure like

Volunteers - ID (Primary Key), LastName, FirstName, other fields for
contact information, etc.

Uniforms - UniformID (Primary Key), VolunteerID (Foreign Key - one of
the values in the Volunteers.ID field), UniformType, and other
information relating to this specific uniform



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Steve]

John,

Thanks for the information.

Yes, I did change the id field in the uniform table so it's not an
auto-number, but too late as the table has already been updated by the
person handling the uniform requirements (different than the person
handleing the volunteer data).

I did not however know to use the primary key, foreign key combination.
Question for you, if I add the foreign key field now, is there a way to
populate that field via query for approximately 500 records and update the
id num? When I populated the table originally, I loaded Last Name, First
Name and email from the volunteer table so I could use criteria where those
fields are all equal. Just a thought.

Thx!

 

[John Spencer]

No, it's too late to do this automatically.

If you have the Last Name, First Name, and email in the uniform table
you could try matching up to that.

At this point I would think that your data is totally unreliable. The
match between the two tables is almost bound to be wrong. And you have
no good idea of which volunteer records match which uniform records.
Some matches may be good, but which ones are they?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================