Removing Lookup Field and Keeping the Data

[shawnvw]

I have a table, "Sessions", that has lookup fields which each pull in a value from the Employee, Checks, and Client tables. (They're each "one" to the Session table's "many". ) Because I'm moving to forms, I want to remove these fields - or at least, not use them any more.

When I get the form set up, it will have combo boxes connecting directly to the Employee, Checks, and Clients tables. But it seems that the next thing that I'll have to do is select the values in each of these fields for every record in "Sessions", right?

Is there a one-time way to retain the data from the lookup fields?

I'm guessing that I would:

1) Create the form before removing the lookup fields from Session,

2) Run some kind of one-time macro that, for each record of Session, instructs the form to "select" the appropriate Employee, Check, and Client based on the value in the lookup fields.

3) Delete the lookup fields from Session.

If I'm right, I'll need help writing that macro. If I'm wrong, then I just need help.

Thanks....

 

[Uncle Gizmo]

Having been awhile since I have had this problem, I guess you would do something similar to the following:

Open the table in design view, select the field in question, and then look down the bottom of your screen and you will see two tabs, one marked "general" and one marked "look up" select the look up tab, and then select --- possibly "text box"

Before you try anything, I suggest you make a backup copy of your database so that if a mistake is made you can recover.

 

[Rich]

Don't use Lookup fields, create separate lookup tables and transfer the data to them

 

[shawnvw]

Open the table in design view, select the field in question, and then look down the bottom of your screen and you will see two tabs, one marked "general" and one marked "look up" select the look up tab, and then select --- possibly "text box"

Okay, I tried it and sucessfully converted the Checks field to a standard field. Now, how do I "look up" this value in the Checks combo box on the form?

Before you try anything, I suggest you make a backup copy of your database so that if a mistake is made you can recover.

Please, I'm not THAT newbie.

 

[Uncle Gizmo]

how do I "look up" this value in the Checks combo box on the form?

I would suggest you have a look at this thread here:
http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619:BlogPost:7029


and this one includes the video clips:
http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619:BlogPost:7031

(Look at Video:Convert contacts Spreadsheet to MS Access database 4 --- 5min)
demonstrates how to create a form in datasheet view, and add a combo box to display the look up data

 

[shawnvw]

I'll take the time to read it. But one thing bothers me:

Everything I've read so far about normalizing a table assumes that -- in my case -- the "Client" field is just a list of client names. In my case, it's not -- its a lookup to a Clients table. If I follow the usual instructions, I think I'll wind up with a new table that has a ClientID column and a Client Name column. But like I said, I already have a Clients table, and every client there has a ClientID already -- and, of course, a name. That seems like too many tables, doesn't it?

How should I modify this guy's instructions for my situation?

 

[neileg]

I'm puzzled. Usually a table level lookup stores the ID from the lookup table but displays the related data. That's why they fail when things get more complex. Surely if you simply remove the lookup definition from the field in table design, you will still be left with the ID values, just the lookup functionality has gone. To retrieve data from your client table you just need a query that joins the two tables on the ID field.

 

[shawnvw]

I'm puzzled. Usually a table level lookup stores the ID from the lookup table but displays the related data...Surely if you simply remove the lookup definition from the field in table design, you will still be left with the ID values, just the lookup functionality has gone.

Yes that's exactly right. Someone else on this board told me I could do this, but I thought I'd be left with the "related data", not the IDs. No one explained what the next step would be. Eventually I figured out a way to convert the related data to the IDs, but you just showed me the easy way. Thanks.

To retrieve data from your client table you just need a query that joins the two tables on the ID field.

Once I realized a way to do the first step, I figured out this second step on my own. But thanks for explaining it; I might still be stuck otherwise.

 

[neileg]

Happy to help