Results 1 to 6 of 6
  1. #1
    ande8698 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    3

    Table's Field uses Table Lookup. Doesn't Query properly

    I have three tables. "Customers," "Sites," and "Township Officials." Each site has a township listed as a field, which relates to a township in the "Township Officials" table. In the sites table, the township field uses a Combo Box, that draws directly from the Township Officials table (Row Source Type: Table/Query).



    Data entry is working great for this relationship. When I go to enter a township in the sites table I see all my townships in the dropdown box that are listed in the "Township Officials" table. That's good. But when I perform a query between the two, my query doesn't pick up all the records that should have a seamless relationship. Please advise.

  2. #2
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Are you only allowing users to pick from something that's in the dropdown or can they still enter their own data? If so, there might be misspellings (or things like extra spaces, etc.) for the listed Township Officials that aren't showing up.

    If the users have to pick someone that already exists in the dropdown, it could be a database corruption issue. Make a backup of the DB and try running a Compact/Repair on it and see what happens.

    If neither of those resolve the issue, you might need to provide some specific examples (or a copy of the DB) for us to look at.

  3. #3
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    This may be the root of your issue.

    http://access.mvps.org/access/lookupfields.htm

    You should have combo boxes and lookups only in forms and never in tables.

  4. #4
    ande8698 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    3
    Quote Originally Posted by alansidman View Post
    This may be the root of your issue.

    http://access.mvps.org/access/lookupfields.htm

    You should have combo boxes and lookups only in forms and never in tables.

    That document does in fact bring light to the root of the issue. What is a little confusing to me is that in this new relational database we have created, we used tons of lookup fields in different tables. I've ran lots of different types of queries between the lookups and only one of them seems to give me trouble. How can that be explained?

    Then also, what is my alternative? The goal of the lookup in the table was to eliminate spelling errors so that queries would work well. Also, I wanted data entry to be quicker with the predictive nature of the lookup. Is there anything else I can do to achieve these things?

  5. #5
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    The resolution is to remove the lookups themselves (without necessarily removing the actual relationships) from your Table while keeping them in the appropriate Forms.

    The point isn't to not use lookup Tables at all, but use them properly.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I agree. I NEVER build lookups in tables. They just cause confusion. When I look at tables I prefer to see the true value retained in the field, not a lookup alias. Even though I know what's happening and could deal with the alias, I just don't bother with setting properties for lookup, format, validation, input mask, caption. I sometimes set Default Value. I always deal with Field Size, Required, Allow Zero Length (always No), Indexed.

    Use queries that join tables to view associated data.

    Build comboboxes and listboxes in forms.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-31-2014, 11:37 AM
  2. Replies: 11
    Last Post: 01-15-2014, 02:02 PM
  3. Replies: 19
    Last Post: 08-08-2013, 01:17 PM
  4. Replies: 3
    Last Post: 03-07-2013, 02:29 PM
  5. Query to lookup relative field from table selection
    By shabbaranks in forum Queries
    Replies: 5
    Last Post: 12-20-2011, 11:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums