Createing look up field

[Joker]

I am converting an Excel workbook into an Access database. This first table
I created was an import of all the original data call it Sales Table. I
want a few of the columns to have a limited set of choices, so I created a
table called Region Table. I created a relationship from the Region Table
to the Region Field in the Sales table. However, when I try and create a
lookup table, it deletes the records of gives me an error indicating I am
putting in an unexpected value. If I go to the Sales Table and select from
the pull down menu, then it accepts the value. Since I have thousands of
lines of data, I don't want to go through each record and re-select the
correct region. Is there a faster or easier way to make this work? Did I
do something wrong on the import or creating the relationship?

 

[Jerry Porter]

I don't understand what you mean by "when I try and create a lookup
table...".
Your Region Table is the lookup table.

If you have created a key field in Regions that you want to use in
Sales instead of the text value of the region, then you can do this
with an update query as follows:

1. Create a RegionID field in sales that is the same data type as the
key field in Regions. I think you've already done this.

2. Create an update query that links the two tables on the Region text
fields.

3. In the Update row, in the column for the Region ID in the Sales
table, enter Regions.RegionID (or whatever you have for the table name
and key field name.)

4. After running the query and verifying the region assignments, you
can delete the region text field from the Sales table.

Jerry

 

[Larry Linson]

. . . However, when I try and create a
lookup table, it deletes the records
of gives me an error indicating I am
putting in an unexpected value. . . .

If you mean "when I try and create a Lookup Field," just don't do that. You
already have your lookup Table, you can join the Tables in a Query and
display the text instead of the ID, it works nicely with the Combo Box, and
there's no question what data you have in which table.

Lookup Fields, on the other hand, are a violation of relational design
principles -- they obscure what is actually stored in the Table by
displaying something else in Table View, and they can be a real pain when
you do Queries.

Larry Linson
Microsoft Access MVP

 

[Jerry Porter]

If you mean that your creating an autolookup query, then make sure that
the region id field is coming from the Sales table, not the Regions
table. It should select from the codes in the Regions table, and assign
that select to the record in the Sales table.

 

[Joker]

I am converting an Excel workbook into an Access database. This first
table

I created was an import of all the original data call it Sales Table. I
want a few of the columns to have a limited set of choices, so I created a
table called Region Table. I created a relationship from the Region Table
to the Region Field in the Sales table. However, when I try and create a
lookup table, it deletes the records of gives me an error indicating I am
putting in an unexpected value. If I go to the Sales Table and select from
the pull down menu, then it accepts the value. Since I have thousands of
lines of data, I don't want to go through each record and re-select the
correct region. Is there a faster or easier way to make this work? Did I
do something wrong on the import or creating the relationship?

I had imported the data and let Access assign the ID, which was unnecessary.
I have corrected the primary key to be the actual data in the file. I had a
copy of the raw data and just copy/pasted the information back in once I had
the relationships established. If you could not tell, I am rather new to
this.