Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    May 2019
    Posts
    65

    Multiple Instances of the same table in one query

    I have a form which uses two tables. In the first table are 4(four) Contact ID Fields which refer to one Contact ID in the Contacts Table.


    In the form, I can only get one of them displaying at a time. The form relationship is setup with just one Contact ID connected to the main table.
    I've tried setting up each field in the form by using 4 instances of the Contacts Table, but that doesn't work.
    I'm sure ore information is needed for you to help, but I thought I would start there.

  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,822
    Purpose of form is input into first table? You want to include the contact table just for display of related info? It should work. Don't use INNER JOIN. You need 4 sets of textboxes to display the 4 sets of contact info. Do not allow edit of contact table fields. Set textboxes Locked Yes and TabStop No.

    However, an alternative is to not include lookup table in RecordSource. Use multi-column comboxes to retrieve contact info. Then textboxes can refer to combobox columns to display info for selected contact.
    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
    Join Date
    May 2019
    Posts
    65
    Thank you for that info. Yes the purpose of the form is input into Table #1 I do have 4 sets of text boxes displaying, however they each refer to the same field in Contacts. I will work with what you suggested and see the result.
    Thank you for responding so quickly.
    Jeff

  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,822
    Including same table more than once in query causes Access to number each instance of table: Table, Table_1, Table_2, Table_3. The textboxes will have to reference the table as a prefix in ControlSource to distinguish between them: [Table_1].[fieldname], etc. This should show in the form's field list.
    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
    May 2019
    Posts
    65
    Ok, This is strange. Out of the four text boxes, only two work - Contacts.[Full Name] & Contacts_1[Full Name].
    When I add Contacts_2 & _3 to the field and then save the form, the two fields change to Contacts_1[Full Name].
    All Contacts_1,2,3 are in the form query to be shown.

    I know I'm so close.
    Thanks,

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to provide db for analysis, follow instructions at bottom of my post.

    Another alternative is to 'normalize' data structure. This would mean a related dependent table that establishes relationship between first table and Contacts with a record for each associated contact.

    I presume you have a Contacts table because each contact can associate with more than one record on the first table?
    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.

  7. #7
    Join Date
    May 2019
    Posts
    65
    Yes I have a Contacts table because each contact can associate with more than one record on the first table. I understand Normalizing the data structure, but I haven't dealt with related dependent tables yet. I will have to get back to you on submitting my DB because it is confidential. I will make a mock up and submit it. Thank you again for your assistance.
    Jeff

  8. #8
    Join Date
    May 2019
    Posts
    65
    I tried to upload the mock-up DB which is very small but is 1.5 MB, but the forum's limit is 500kb. How do you suggest that I send it?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Zip file up to 2MB allowed.
    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.

  10. #10
    Join Date
    May 2019
    Posts
    65

    RC Shelter - Multiple Instances of same Tiable in Query

    The problem I'm currently having is that the form doesn't save correctly. Text Boxes that contain Contacts_1.[Last Name] & 2 turn into 3. I can change this by changing the order on the form query. I can get Contacts.[Last Name] and _1 working when there is no 2 or three in the query. But soon as I add the third field it won't save correctly.
    This is a volunteer project for the Red Cross and if I get this part of the form working then I can move on to the rest of the application. The data is all fictitious and I only provided the form and tables that I am having trouble with. The fields in question are contacts to use, open, alt1 and alt2 and they're at the bottom left of the form. The labels for Open, Alt1 and Alt2 are still default text labels.

    Also, notice if you can only change two of the values on the right and one of them changes two or three of the fields on the left. The order of the fields should be

    USE - Contacts.[Last Name] Which is the contact to Use. They are numbered 1 through 6. See on the right.
    Text 47 - Contacts_1.[Last Name] Which is the contact to Open the shelter.
    Text 49 - Contacts_2.[Last Name] Which is an alternate contact
    Text 51 - Contacts_3.[Last Name] Which is a second alternate.
    Right now there mixed up as I was experimenting with re-ordering them.



    Thank you for your help.

    Jeff
    The RC Shelter
    Attached Files Attached Files

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Rather than repeating fields, ie.- contacts_1,contacts_2,contacts_3,contacts_4,
    you may want a table of shelters, a table of contacts, and a junction table of contacts to shelters.
    Then you can use a subform with a master/child link on shelter ID.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, I give up. Recommend alternate approach of multi-column combobox and textboxes referencing combobox columns.

    Really should be comboboxes instead of textboxes for the 4 ShelterTable fields anyway.

    Eliminates including Contacts table in form RecordSource.

    Advise not to use spaces nor punctuation/special characters in naming convention.

    moke123 is also recommending the 'related dependent' table I described earlier. Then a form/subform arrangement would be used.
    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.

  13. #13
    Join Date
    May 2019
    Posts
    65

    Thank you both

    Thank you. I was wondering if I had an approach that wasn’t acceptable to Access. I will revise the tables/fields/queries as you and June 7 have suggested. Thank you both for the help.

    Quote Originally Posted by moke123 View Post
    Rather than repeating fields, ie.- contacts_1,contacts_2,contacts_3,contacts_4,
    you may want a table of shelters, a table of contacts, and a junction table of contacts to shelters.
    Then you can use a subform with a master/child link on shelter ID.

  14. #14
    Join Date
    May 2019
    Posts
    65
    I just have one “last” question. When you create relationships which are “universal” to the entire program, do queries supercede or add to those universa relationships?
    Again, thanks for th help.
    Jeff

    Quote Originally Posted by June7 View Post
    Okay, I give up. Recommend alternate approach of multi-column combobox and textboxes referencing combobox columns.

    Really should be comboboxes instead of textboxes for the 4 ShelterTable fields anyway.

    Eliminates including Contacts table in form RecordSource.

    Advise not to use spaces nor punctuation/special characters in naming convention.

    moke123 is also recommending the 'related dependent' table I described earlier. Then a form/subform arrangement would be used.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works." Only you can decide how far to go.

    Current arrangement can work. Just need to understand limitations as well as advantages before deciding. For instance, searching all 4 fields for a contact name is trickier.

    Building relationships in the Relationships dialog is helpful for building queries and managing data integrity. However, you can build queries with relationships not set up in the dialog.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. run multiple instances of the same database
    By chriswrcg in forum Access
    Replies: 1
    Last Post: 05-08-2019, 07:03 AM
  2. Replies: 7
    Last Post: 06-16-2016, 03:05 PM
  3. Changes to table layout by multiple users or instances
    By Jennifer Murphy in forum Access
    Replies: 5
    Last Post: 05-01-2014, 09:18 AM
  4. Counting instances across multiple tables
    By New_2_Access in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 04:47 PM
  5. Multiple Payment Instances
    By luckysarea in forum Queries
    Replies: 3
    Last Post: 04-21-2011, 03:29 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