Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Data Look Up Relationships (Access 2003)

    I have multiple tables, and I wanted to get help making sure that I am doing the relationships correctly. I have a main table that I need to pull information from other table using the drop down list, however once it is populated into the main table field I do not want it to be effected by changes in the other tables. Can someone refresh my memory on how I go about doing this, I have books I should be able to reference on doing this but they are packed up and I need to do this now. Thank you for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Look Up Relationships (Access 2003)

    Welcome to Woody's Lounge!

    Open the main table in design view.
    Click in a lookup field.
    Activate the Lookup tab in the lower part of the window.
    Set the Display Control property to Combo Box.
    Row Source Type should be set to Table/Query (the default).
    Row Source must be the name of a table or query containing the values you want to select from, or an SQL string such as

    SELECT FieldName FROM TableName ORDER BY FieldName

    Make sure that the Limit to List property is set to No. This will make it possible to have values that are not in the list (for example because the list changed).
    That's it, more or less!

  3. #3
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Data Look Up Relationships (Access 2003)

    Thank you. My account expired so I had to create a new one, but thank you for the welcome back.
    I understand what you sent but if I choose to lookup from the table in the way your describe, will it not change my data when I update the table I am pulling my data from, or is it actually placing it in the main table indifferent from changes in the other table because it is not limited to the list?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data Look Up Relationships (Access 2003)

    For this to work, your main table must be storing the value you want to keep, and not the ID# of a record from the lookup table, which might be changed. You would of course have no relationship defined between the main table and the lookup table.

    For example, your lookup table might consist of a ColorID (the PK) and a ColorName, such as:
    1 Red
    2 White
    3 Blue

    On the data entry form for your main table, the combobox for data selection would only include the colornames, not the ID. This way, if you select White for a particular record, it will always stay white, even if the colorname for colorID=2 is changed to Navaho White.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Data Look Up Relationships (Access 2003)

    Thank you. I will try what you and HansV and post back if I have any trouble. Thanks again for your help.

  6. #6
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Data Look Up Relationships (Access 2003)

    I have three tables,
    1----Is Location (it has building numbers and information about the department),
    2----Is a list of representatives who are allowed to submit requests for the buildings
    3----Is a record of the requests.

    I have TABLE 3, doing a lookup from TABLE 1, for the Building, then based on the Building I need to lookup the representatives that can submit requests.
    The representatives are broke down into three categories SUB, MID, EXEC for there function in the request process. Then each one of them is assigned a Zone covering the area they are authorized to submit requests for. In TABLE 1 I made a column for SUB, MID and EXEC and assigned a Zone Number to each Bldg.

    I am trying to create a query that will look at the Bldg I listed in TABLE 3, and then based on the Zone associated to the bldg in TABLE 1, give me a list of the representatives for each category to fill in TABLE 3.

    Example:

    Table 1(Location)
    Bldg 1 Sub Zone=1 Mid Zone=4 Exec Zone=2
    Bldg 2 Sub Zone=2 Mid Zone=2 Exec Zone=1

    Table 2 (Reps)
    Jane Smith Type=Sub Zone=2
    John Black Type=Exec Zone=1
    Larry Johnson Type=Mid Zone=2
    Martha Williams Type=Sub Zone =1

    Table 3 (Requests)
    Request 1 for building #2 (looked up from Table 1 (Location))
    Feild "SUB" I want to pull a list of the Type = SUB with the Zone = 2 (i.e. Jane Smith and not Martha Williams) in my drop down
    Feild "MID" I want to pull a list of the Type = MID with the Zone = 2 (i.e. Larry Johnson) in my drop down
    Feild "EXEC" I want to pull a list of the Type = EXEC with the Zone = 1 (i.e. John Black) in my drop down

    Once I have selected a field from the lookup, I want it to remain in Table #3 and be unchanged by updates in the other tables.

    I am thinking that I may have structured the database harder than I needed with these three tables. Any help would be appreciated. Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Look Up Relationships (Access 2003)

    I'd use some extra tables - see the attached version.

    I have created a small form on which you can enter details for a request (the request should already exist, I didn't build a form for that).
    The combo box for the representative has a query as row source that refers to the request and category (Sub, Mid, Exec) selected on the form.
    There is code in the On Current event of the form and in the After Update event of the combo boxes for request and category to requery (update) the representative combo box.

  8. #8
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Data Look Up Relationships (Access 2003)

    Thank you for your help and effort, I will work with what you gave me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •