Field Type Change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 14 Jun 2007 16:49 GMT

I am trying to change a free text field to a drop down menu in my
current database, that already has data entered for this particular
field, in order to avoid multiple versions of the same organization
that are just typed in differently.

I'm assuming that I'll need to recode the different versions of an
entry to make them uniform but after that, I'm unsure how to proceed
so I won't lose any data. Is this possible or will I need to delete
the field, recreate it as a drop down and then re-enter the data?

Thanks in advance!

Mike

Reply to this Message

Douglas J. Steele - 14 Jun 2007 17:11 GMT

Hopefully you aren't talking about that woefully misguided "feature", the
lookup field. (See http://www.mvps.org/access/lookupfields.htm at "The
Access Web" for some of the reasons why most of us feel this way)

Your users should never be working directly with the table: they should
always have a form. When you've got a form, you can easily have a combo box
on that form that binds to that currently free-form text box, thus supplying
it with the appropriate text.

As you say, clean up your existing data, then create a table that has one
row for each unique value you want to allow in that free-form field. In this
way, you do not have to make any change to your existing table, and
everything will continue fine.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am trying to change a free text field to a drop down menu in my
> current database, that already has data entered for this particular
[quoted text clipped - 9 lines]
>
> Mike

Reply to this Message

Mike - 18 Jun 2007 17:55 GMT

Thanks for all the input and advice so for. To clarify - the change
that I need to make is to change a free-text field in a form to a drop
down with 11 choices. From what I can gather so far, I need to create
a new table with the 11 responses I want to allow and link that to the
corresponding field in the form. At this point is where I am getting
lost again. If I standardize my already entered data, can I somehow
link it to the new table that I create so I don't have to re-enter the
data?

Thanks again,

Mike

On Jun 14, 9:11 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> Hopefully you aren't talking about that woefully misguided "feature", the
> lookup field. (Seehttp://www.mvps.org/access/lookupfields.htmat "The
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Reply to this Message

Douglas J. Steele - 18 Jun 2007 18:59 GMT

You have 2 main options.

You can store the text in a 2nd table, with each row having an ID associated
with it, and only store the ID in the existing table, or you can keep the
text in the existing table (while still having the 2nd table for
standardization)

Either way, you need some way of ensuring that you can map from the existing
text to the standardized text. You can do this either by manually changing
what's already there to the standard text, or by creating a cross-reference
("The text in row 5 is supposed to be stock phrase 8")

Once you correct the text in the existing table, you've essentially done all
you have to do if you're keeping the text in that table. If you only want to
store the ID of the stock phrase in the table, you'll need to run an Update
query to add the ID field to the existing table. Once you've done that, you
can delete the text from the existing table.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for all the input and advice so for. To clarify - the change
> that I need to make is to change a free-text field in a form to a drop
[quoted text clipped - 47 lines]
>>
>> - Show quoted text -

Reply to this Message