Results 1 to 5 of 5
  1. #1
    JAMIAM is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2016
    Posts
    3

    Editable query on single table from fields on multiple tables

    I have three tables:
    Contacts; id, Client_SSN, client_name

    Family; id, Family_SSN, family_name

    Accounts. SSN, AcctNumber


    I would like to create a query that will search for all accounts associated with a household (i.e. the accounts with SSN equal to Client_SSN and Family_SSN), but the results must be editable as it will be the basis of a subform.

    A union query got me the results I wanted, but wasn't editable. Same when I used an OR statement (I think the issue is that there are three tables?).

    Perhaps if I could create a query that gets Client_SSN and Family_SSN in one field, then do my select query based on that? I'm a complete novice and completely lost.


    Next, I will need to incorporate a Business table that has Tax_ID field as well to include those accounts. But first things first.

    Any help is greatly appreciated!

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Rather than doing this at the query level - instead it is accomplished at the form object level. Make a Main form bound to the appropriate table, and inset into it 2 separate sub forms each bound to their table. This will then be editable.

    Note the main form can not be a continuous form, it must be single form. The sub forms can be continuous if that is appropriate. In the main form you insert a combobox in the header to provide an quick selection method for the user.

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure of your whole process or the relationships you have there. Do you have 1 Client that can have multiple Families that can have multiple Accounts? Or is Client and Family the same thing? Is this data already in tables or will you be entering this data in? Can you give examples of data in those tables you listed.

  4. #4
    JAMIAM is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2016
    Posts
    3
    Quote Originally Posted by NTC View Post
    Rather than doing this at the query level - instead it is accomplished at the form object level. Make a Main form bound to the appropriate table, and inset into it 2 separate sub forms each bound to their table. This will then be editable.

    Note the main form can not be a continuous form, it must be single form. The sub forms can be continuous if that is appropriate. In the main form you insert a combobox in the header to provide an quick selection method for the user.
    This is how I originally set it up but I'm trying to avoid having multiple subforms for this one task as my main form is already bloated. There's would have to be 3 subforms just to see a client's related accounts (client's accounts, family member's accounts, business accounts) and I'm not aware of a way to get those to appear as one.

    I was able to create a workaround and get all related accounts into one subform last night with a series of queries that appends the SSN for the client, SSNs for each family member, and tax-ids for client's businesses to a temporary table, which allows the SSNs to be the primary key so that I can use that table to reference the related accounts and still be editable. This table has to be cleared and refilled every time a new client is selected in the form since a business could be owned by multiple clients (keeping the SSN from being the primary key) but it works for now. I then created a macro to run the series of queries and have that run every time a new client is selected on the form. Clunky for sure, but it seems to work.

    Thanks for your input!!

  5. #5
    JAMIAM is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2016
    Posts
    3
    Quote Originally Posted by Bulzie View Post
    Not sure of your whole process or the relationships you have there. Do you have 1 Client that can have multiple Families that can have multiple Accounts? Or is Client and Family the same thing? Is this data already in tables or will you be entering this data in? Can you give examples of data in those tables you listed.
    Apologies for not being more clear. Each client will have multiple family members. Each client can have multiple accounts and family members may or may not also have multiple accounts. Some family members may be in the contact form, but some might not be. Unfortunately, the Contacts form is based on data exported from our CRM and the Accounts table is directly from another unrelated system. Here's a quick example of what it may look like:

    Contacts:
    id Client_SSN client_name
    1 111-11-1111 John Smith
    2 222-22-2222 Jack Johnson
    3 333-33-3333 Joe Mama
    4 444-44-4444 Kelly Kapowski

    Family:
    id Family_SSN family_name relation
    1 555-55-5555 Jane Smith Wife
    1 666-66-6666 Jack Smith Son
    1 777-77-7777 Jessica Smith Daughter
    2 888-88-8888 Phil Johnson Son
    2 999-99-9999 Doug Johnson Son

    Accounts:
    SSN AcctNumber
    111-11-1111 1432-4231
    111-11-1111 9408-3425
    222-22-2222 3456-3453
    333-33-3333 2345-1368
    444-44-4444 6324-9984
    555-55-5555 1234-3455
    666-66-6666 2345-9989
    999-99-9999 9840-5943


    So when I'm editing the form for John Smith, I want a single subform that will show the accounts related to SSNs 111-11-1111, 555-55-5555, 666-66-6666, and 777-77-7777. The primary key for Contacts is "id" and the primary key for Accounts is "AcctNumber" but Family does not have a primary key since each entry can be related to multiple clients on the Contacts table and each client can have multiple family members. See below the description of the workaround I came up with, which I mentioned in the previous reply:

    "I was able to create a workaround and get all related accounts into one subform last night with a series of queries that appends the SSN for the client, SSNs for each family member, and tax-ids for client's businesses to a temporary table, which allows the SSNs to be the primary key so that I can use that table to reference the related accounts and still be editable. This table has to be cleared and refilled every time a new client is selected in the form since a business could be owned by multiple clients (keeping the SSN from being the primary key) but it works for now. I then created a macro to run the series of queries and have that run every time a new client is selected on the form. Clunky for sure, but it seems to work."

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

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2014, 05:49 PM
  2. Replies: 1
    Last Post: 04-12-2013, 03:03 PM
  3. Replies: 1
    Last Post: 08-30-2011, 07:35 AM
  4. Replies: 4
    Last Post: 04-09-2010, 02:16 AM
  5. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM

Tags for this Thread

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