Look-up blues

[kupe]

I want to change a table that had two or three combo box choices to look-up lists.

But it will mean that the fields that were completed become vacant - and it looks a massive job to input all that information.

Be good to code all that's in the fields 'Work' and 'Race' and 'Something' from tblOriginal into those fields in tblRevised.

However, the properties for the fields change from text to numbers the moment they become look-up lists. Is there a way round this, please?

 

[WayneRyan]

kupe,

Don't understand the part about text changing to numbers, but the
rest should be straight-forward.

Insert Into tblRevised(NewKey)
Select Distinct(OldKey)
From   tblOriginal

Wayne

 

[kupe]

Thanks, Wayne. I'm treading unfamiliar territory here. The story so far ...

An important database has one main table - tblOriginal. It works ok but it doesn't follow the relational idea.

I have a few days when the database isn't in use. I thought I would try to split it up into one-into-many tables. So one of the first changes I have tried is to make the lookup options belong to a separate small table.

When the lookup options belong to the same table, the data type is text. When I have the lookup options coming from a separate table, the data type becomes Numbers.

How can I INSERT INTO if the data type is different?

And if I can't do this by coding I might as well abandon the effort because to insert into manually would take too long.

Be very grateful to hear how you would tackle this.

 

[Oldsoftboss]

First you need to do is create your look up table and enter the values you want. (AutoNo, FieldName)

Once this is done, run an update query on the field in the original table, updating the original text to the corrisponding AutoNo field in your look up table.

Once you have updated each name you can change the data type in the original table to Number. Remember to remove the 0 from the default value of this field.

Then create the relationship etc.

Dave

(Rememer to make a backup first but )


.

 

[kupe]

Thanks very much, Dave. There are 24 lookup options. Would you do that one at a time?

 

[Oldsoftboss]

You can do them all at the same time, just be sure to create the lookup table first and be sure you reference them carefully and correctly.

Dave

 

[kupe]

Thanks, Dave. Yes, I got through the first part ok, thanks to your guidance.

That plan is to split the database into three tables instead of the solitary main table. With your help, I have impressive one to many links with the look-up tables. I want to link these three tables together, but at the last attempt, I could gain no more than indeterminate links.

I'll try again when the database is free again for a few days. All the best. Thanks very much for the zip.

 

[Oldsoftboss]

Be sure you have set the ID field in your lookup table as the primary key.

 

[kupe]

Yes, I did that, thanks, and the links to those tables look good.

Those tables provide the look-ups for things like work and race and each has lots of entries. What I want to have is a table with each employee's personal details, and one with his working details and another if he wants to go to another part of the factory.

At the moment, all this is on one table. But to make the db relational, it seemed the three tables was the way to go, plus the look-up tables. I didn't have time to pursue the three important tables. But initially it seemed that there was going to be no easy one-to-many route for them.

I hope to be able to get back to it soon. All the best, Dave, and thanks for the advice.