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

Help changing Field Values: Text to Number

P: n/a
I have 2 tables, CASE and ISSUE. My table, CASE, stores the issues
pertaining to that particular case. The table, CASE, was designed with
2 fields, IssueID and IssueDesc. Right now, all of the issues are
stord in the field, IssueDesc. BUT I'd like to change that so that the
issues are stored in the IssueID field.

On my form, the "Control Source" was linked to IssueDesc. So, the
issues the user selected are stored in the field, IssueDesc, in the
table, CASE.

Please see the Example below.

*************************************
Table: ISSUE

IssueID = Primary Key
IssueDesc (Text)

*************************************

Example of ISSUE Table:

IssueID IssueDesc
1 Age
2 Sexual Harassment
3 Termination
*************************************
Table: CASE

CaseID = Primary Key
IssueID (Number)
IssueDesc (Text)
*************************************

Example of CASE Table:

CaseID IssueID IssueDesc
200 0 Age
300 0 Sexual Harassment
400 0 Termination
*************************************

Do you see how there are "0's" in the IssueID field in CASE? That's
because the "Control Source" on the form was linked to that field.

I would like to get rid of the IssueDesc field from the table, CASE,
and only store the user's selection in IssueID.

Is there any way to match the correct IssueID with that matching
IssueDesc in my table, CASE? Or will I have to go back and re-select
the issue so that IssueID is stored?

Thanks soo much!

Megan
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Assuming your IssueDesc field values are the same in both tables you
can use an SQL update statement on a join of the two tables:

UPDATE Issue RIGHT JOIN Case ON Issue.IssueDesc = Case.IssueDesc SET
Case.IssueID = Issue.IssueID;

That should update all IssueID fields in the Case table and then you
can safely drop the IssueDesc field. HTH

Rick Collard
www.msc-lims.com
On 30 Apr 2004 10:55:30 -0700, me**************@hotmail.com (Megan)
wrote:
I have 2 tables, CASE and ISSUE. My table, CASE, stores the issues
pertaining to that particular case. The table, CASE, was designed with
2 fields, IssueID and IssueDesc. Right now, all of the issues are
stord in the field, IssueDesc. BUT I'd like to change that so that the
issues are stored in the IssueID field.

On my form, the "Control Source" was linked to IssueDesc. So, the
issues the user selected are stored in the field, IssueDesc, in the
table, CASE.

Please see the Example below.

*************************************
Table: ISSUE

IssueID = Primary Key
IssueDesc (Text)

*************************************

Example of ISSUE Table:

IssueID IssueDesc
1 Age
2 Sexual Harassment
3 Termination
*************************************
Table: CASE

CaseID = Primary Key
IssueID (Number)
IssueDesc (Text)
*************************************

Example of CASE Table:

CaseID IssueID IssueDesc
200 0 Age
300 0 Sexual Harassment
400 0 Termination
*************************************

Do you see how there are "0's" in the IssueID field in CASE? That's
because the "Control Source" on the form was linked to that field.

I would like to get rid of the IssueDesc field from the table, CASE,
and only store the user's selection in IssueID.

Is there any way to match the correct IssueID with that matching
IssueDesc in my table, CASE? Or will I have to go back and re-select
the issue so that IssueID is stored?

Thanks soo much!

Megan


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.