Help With Another UPDATE uery Please!

[Shaun]

Hi,

I am making some alterations to my Database. I have a table called projects
and a table called Work_Types. Projects currently contains the name of the
work type (Work_Type) but now I want to change this so it contains the
Work_Type_ID, is it possible to update Projects with one query?

Thanks for your help

[U N Me]

Shaun wrote:

Hi,

I am making some alterations to my Database. I have a table called projects
and a table called Work_Types. Projects currently contains the name of the
work type (Work_Type) but now I want to change this so it contains the
Work_Type_ID, is it possible to update Projects with one query?

Thanks for your help

You could create a new field called Work_Type_ID in Projects. Then create a
new query and drop in both tables. The relationship between the two tables
should be between Work_Types. I assume table WorkTypes contains 2 fields;
WorkTypeID (numeric) and WorkType (text). Drag down WorkTypeID from Projects,
set the query to Update, and in the UpdateTo row enter Work_Type!Work_Type_ID.
Now run the query.

Now open up the table Projects. Delete the field Work_Type.. Save the table.
Now rename the field Work_Type_ID to Work_Type if that is what you want.

[Pieter Linden]

"Shaun" <sh***@mania.plus.com> wrote in message news:<tj******************@wards.force9.net>...

Hi,

I am making some alterations to my Database. I have a table called projects
and a table called Work_Types. Projects currently contains the name of the
work type (Work_Type) but now I want to change this so it contains the
Work_Type_ID, is it possible to update Projects with one query?

Thanks for your help

Does it really matter if it can be done with one query? No, you
can't. you need a SELECT DISTINCT... query to get the unique values
from the WorkTypes field, and then you have to write those values to a
table with something like WorkTypeID(AutoNumber) and WorkType(text).
Then you could add the WorkTypeID field to your original table and
update it with an inner join on Work_Types, joining on WorkType (the
text value). Shouldn't take 2 minutes if you know what you're doing.