Help changing Field Values: Text to Number

[Megan]

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

[Rick Collard]

Re: Help changing Field Values: Text to Number

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

https://www.msc-lims.com

On 30 Apr 2004 10:55:30 -0700, meganrobertson2 2@hotmail.com (Megan)
wrote:
[color=blue]
>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[/color]