Createing look up field

#1 (**permalink**) 28-Jul-2006, 08:24 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11862-createing-look-up-field.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
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?
--
Joker
"...God hath made me to laugh, so that all that hear will laugh with me."
Gen. 21:6

#2 (**permalink**) 28-Jul-2006, 08:24 AM

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.)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862

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

Jerry

Joker nospam wrote:
> 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
> --
> Joker
> "...God hath made me to laugh, so that all that hear will laugh with me."
> Gen. 21:6

#3 (**permalink**) 28-Jul-2006, 08:24 AM

"Joker" wrote

> . . . However, when I try and create a
> lookup table, it deletes the records
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
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

#4 (**permalink**) 28-Jul-2006, 08:24 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
table. It should select from the codes in the Regions table, and assign
that select to the record in the Sales table.

#5 (**permalink**) 28-Jul-2006, 08:24 AM

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11862
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.

--
Joker
"...God hath made me to laugh, so that all that hear will laugh with me."
Gen. 21:6