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]

Re: Help With Another UPDATE uery Please!

Shaun wrote:
[color=blue]
> 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[/color]

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]

Re: Help With Another UPDATE uery Please!

"Shaun" <shaun@mania.pl us.com> wrote in message news:<tjzfb.333 2$kA.893364@war ds.force9.net>. ..[color=blue]
> 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[/color]

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(Auto Number) 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.