CODEWITHSUNDEEP
ms-accesslookup
ndthl
ndthl

Reputation: 173

applying a lookup to a field deletes values already in the field (MS Access 2010)

I came across an excellent lookup wizard tutorial:

http://www.jegsworks.com/lessons/databases/basics/step-lookupwizard.htm

I have a difficult situation though, because my table already has data in the field on which I am applying the lookup. This is because I have a table i'm importing from Excel. This already contains the name of the staff member who created each record in the record_created_by field. After the import, I want to apply a lookup to retrieve staff name values from tblStaff_members. This will make future data entry is easier, the user just has to select from the list. However, when I apply the lookup, Access wants to delete the values already in the field in the table I imported.

This means I have to update the field with the data that was in there. I could do it manually with the lookup dropdown menu, but this would take a long time for the 500+ records.

One way I thought of doing this was to use an update query, since the field, presently, has only one value throughout - my name (this is a field tracking who has created each record, values may change in future as other staff members update the table). I found, however, that an update query does not work on a field with a lookup already applied.

Any way out of this difficulty?

Wil

Upvotes: 1

Views: 1467

Answers (2)

Francis Rodgers
Francis Rodgers

Reputation: 4685

I asked a similar question here but then I discovered your question. While getting my question answered I discovered the steps that may help you so I provide them here for my own reference and your consideration. (I use access 2013 so the steps may be different in other versions).

Step 1. Import the data from excel and setup your cols as required. Step 2. Run the following Query

SELECT DISTINCT [OldTableName.FieldName] INTO [NewTableName]
FROM [OldTableName];

Step 3. When it gives you the results you want run it as a Make Table query and it will create the table with the data you require in it.

Step 4. Once the new table is created go into it and do what you require. Personally I did nothing as I did not want to store that data as numbers but for a variety of reasons some people might. So since the data was unique I just set the text data in the new table as my primary key and set a few other properties as needed and left it at that.

Step 5. I went back into my old table and used the Lookup Wizard to create the relationships and links and then set the properties as needed. There are other ways to setup the combobox which are recommended and you can use them if you desire but this was good enough for my needs.

I hope this is helpful.

Upvotes: -2

Bill
Bill

Reputation: 1

The only way I have been able to get around this is to create another data field (column) and create the lookup in the new field. By using the drop down selection duplicate the records from the original field. When you are done delete the original field.

Time consuming... but it works.

Upvotes: 0

Related Questions