Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Createing look up field

Discussion in 'Information Technology' started by Joker, Jul 28, 2006.

  1. Joker

    Joker
    Expand Collapse
    Guest

    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?
    --
    Joker
    "...God hath made me to laugh, so that all that hear will laugh with me."
    Gen. 21:6
     
  2. Loading...


  3. Jerry Porter

    Jerry Porter
    Expand Collapse
    Guest

    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



    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?
    > --
    > Joker
    > "...God hath made me to laugh, so that all that hear will laugh with me."
    > Gen. 21:6
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "Joker" wrote

    > . . . 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
     
  5. Jerry Porter

    Jerry Porter
    Expand Collapse
    Guest

    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.
     
  6. Joker

    Joker
    Expand Collapse
    Guest

    >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.

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

Share This Page