By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

Help With Another UPDATE uery Please!

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Nov 12 '05 #2

P: n/a
"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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.