Results 1 to 7 of 7
  1. #1
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98

    Search Form - With relationship to tables

    Greetings all,

    I am revamping my database and rebuilding most of it from the ground up so it can be more robust and more user friendly.
    I have created a few tables with miscellaneous data just for testing as I am designing everything.

    For starters, when creating and defining my tables, I used the "Lookup Wizard" under the Data Type which allows me to select the reference and automatically creates the relationship link between the two.


    However, when doing this, sometimes it references a number that refers to the name in the field. As an example, let's say that Company A has an ID of 1, Company B is 2, etc.

    When I am creating a query to search for the company name, it seems to be attempting to reference the ID number associated with the company name and the resulting search results are blank because I am trying to search for Company A rather than the number 1.

    Is there a way to mask this search value so that it will allow the user to search for the company name rather than the unidentified number?

    I hope I explained this thoroughly enough. Those of you familiar with relationships most likely know what I am referring to.
    I recall seeing a tutorial related to this in the past, but I cannot locate it again.

    Thanks.

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    http://access.mvps.org/access/lookupfields.htm

    Do your lookups in Forms and not in tables.

  3. #3
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by alansidman View Post
    http://access.mvps.org/access/lookupfields.htm

    Do your lookups in Forms and not in tables.
    Thank you for the reply.
    I was initially contemplating using the combo box in the search form and now it looks like I will.

    Thanks.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also suggest NOT using calculated fields in tables, nor Multi-value fields.

  5. #5
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by ssanfu View Post
    Also suggest NOT using calculated fields in tables, nor Multi-value fields.
    I'm not certain what that means... calculated fields?
    Why is multi-value fields in a table a bad idea? I assume you are referring to a drop-down list like a State selection or something similar?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    calculated fields,
    multi-value fields in a table
    these are common terms in Access and definitions and usage examples easily found using Google or Bing etc.
    Why is multi-value fields in a table a bad idea? Did you read the link in AlanSidman's post #2?
    If you search the forum for posts/threads referencing "multi-valued fields", you will get an understanding of related issues.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Solved? Could you post a description of the solution? It may help others.
    Glad you have resolved the issue.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-27-2017, 04:05 PM
  2. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  3. Replies: 29
    Last Post: 03-11-2016, 08:44 PM
  4. Replies: 5
    Last Post: 11-26-2013, 11:11 PM
  5. Replies: 17
    Last Post: 08-21-2013, 12:52 PM

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