Results 1 to 9 of 9
  1. #1
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16

    Best Way to Query Two Tables based on a Person's Name

    I want to create a Query based on people's names.

    In one multiple record table (Table 2), I want to select people's names from a single record table (table 1), and pull associated data from table 1 into the records in table 2.

    One way I tried this was to create a name query for Table 1 and attach it as a Lookup field in Table 2. I have since seen that Other Table/Query Lookups are ill-advised. Also, I can't create a Criteria Query for the name in Table 2 (probably because it's based on a Lookup).

    Currently, I am using an internal "I type the values" lookup in each table and using combo boxes in the associated Forms. That works, but will be labor intensive going forward as I'll have to manually update the fields in both tables for each new entry in Table 1.

    Is there a cleaner way to do this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Do these tables have relationship? Build query joining tables.

    Should not be saving people's names into Table2, just ID of record from Table1.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  3. #3
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16
    June7, I would like to use relationships. But I can seem to get it right. The only tutorials I've been able to find don't seem practical.

    The tutorials I've seen discuss (for example) relating the primary key of Table 1 to a foreign key in Table 2 and then entering the ID of Table 1 as a number in Table 2.
    That would mean my database users would have to remember that "Joe Public" is "17" while "Jane Doe" is "8".

    Is there a way to achieve this using the names?

    Thanks for your help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Users don't have to remember anything. They don't even have to be aware of the keys. Build combobox so users see names but ID is saved. This is basic functionality.

    Look for tutorial on "multi-column combobox or listbox".

    As an example, consider these properties of combobox:

    RowSource: SELECT EmpID, LastName & ", " & FirstName AS FullName FROM Employees ORDER BY LastName, FirstName;
    ControlSource: EmpID_FK (field from Table2)
    BoundColumn: 1
    ColumnCount: 2
    ColumnWidths: 0";1"
    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.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    An example why it is a bad idea to use names to connect tables: you can have several different people with name like John Smith - how do you make your queries to connect every one of them with right data!

  6. #6
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16
    Thanks again for your help.

    I got it to work somewhat. But it's still storing as a number on that second table.

    The trick is, I need to pull the name from Table 1 and then add new data to Table 2.

    So on the related form, I need the end user to pull the Table 1 names and data, and then fill out the remaining data in Table 2.

    Example:

    Table 1: [Employee Name] [Class1 Date] [Class2 Date]

    Table 2: [Employee Name] (user selects from Table 1 Dropdown) [Class1 Date] [Class2 Date] (display from Table 1) [Employee Performance Notes] (User adds to Table 2)

    Am I asking too much from a Select Query? I tried Append and Update queries, but neither of them were doing what I wanted.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If each employee has only one record in Table 2, then Table 2 is entirely unnecessary. Just add the Performance Notes field to Table 1.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That data structure makes no sense to me. Certainly doesn't alter my advice - don't save name, save ID.

    If you need to find an existing record in table2, then apply filter criteria. Review http://allenbrowne.com/ser-62.html
    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.

  9. #9
    Danelloc is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2023
    Posts
    16
    Quote Originally Posted by June7 View Post
    That data structure makes no sense to me. Certainly doesn't alter my advice - don't save name, save ID.

    If you need to find an existing record in table2, then apply filter criteria. Review http://allenbrowne.com/ser-62.html
    Thanks again for your help. I agree, the data structure wasn't right. I made some changes and your idea worked like a charm.

    Thanks again.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Replies: 2
    Last Post: 06-30-2016, 06:38 PM
  3. Replies: 19
    Last Post: 01-24-2016, 07:48 PM
  4. Replies: 5
    Last Post: 07-28-2014, 04:05 PM
  5. Replies: 3
    Last Post: 05-06-2014, 11:38 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