Results 1 to 5 of 5
  1. #1
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6

    Post multiple fields in one table to be linked to a separate table


    I am trying to create a form showing people who all play multiple musical instruments. I have a table of people with three fields for instruments and a table consisting of a list of instruments. If I make a query (to base the form on) to show the people and the instruments they play, it works ok if I link one instrument field to the instrument table, but if I link two or more different instrument fields to the same table, everything comes up blank. I feel that I must be making some trivial mistake. Can anyone help me please?

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    You have to add the instrument table one time for each link. So if you have three fields for instruments, you have to add the instrument table three times, with one link to one field for each table.

    Then, when you add the Instrument Name value from the Instruments table, you can give it an alias to differentiate from the others. Otherwise, Access will use the Tablename.Fieldname format for your field name, which is not ideal.

    For example, for your instance of the Instrument table that is linked to the first field, you can call it "Instrument 1." So in the Instrument Name field from the first Instrument table instance, you'd put:

    Code:
    Instrument 1: [Instrument Name]
    to give it the "Instrument 1" alias (and assuming the field is called "Instrument Name" of course).

    That being said, a better approach would be to just use a child table for your instruments, instead of having three fields. That way, you could enter as many instruments as you'd like, and you'd only have to deal with one field instead of three.

  3. #3
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Also, make sure you use outer joins from your people table to each of your instrument tables. Otherwise records will drop out if any instrument fields are blank.

  4. #4
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    Thank you so much for the quick reply. I am very impressed. I will try out your suggestions tomorrow. I'll let you know how it works.

  5. #5
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    It turns out that by making all the links 'outer joins' that solved the problem. I haven't tried the child table approach - not sure how to do that, but I don't really need more than three entries. Again thanks so much for this valuable lesson.

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

Similar Threads

  1. extract multiple values from a cell to a separate table?
    By benlogo in forum Import/Export Data
    Replies: 4
    Last Post: 08-18-2015, 06:35 AM
  2. Replies: 18
    Last Post: 05-07-2015, 10:59 AM
  3. Replies: 3
    Last Post: 04-29-2015, 04:02 PM
  4. Replies: 12
    Last Post: 12-14-2011, 08:04 PM
  5. Replies: 3
    Last Post: 08-16-2011, 02:20 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