Results 1 to 7 of 7
  1. #1
    siema24 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    22

    Problem with relations (allow multiple values)

    Hello,


    I have a small problem with creating a relations between two tables. Maybe you guys can help, I'll be very thankful!

    I've got two tables "tblA" and "tblB" with fields:

    tblA:
    -Id (primary key, indexed, no duplicats). values: 1,2,3,4

    tblB:
    -Id(primary key)
    -ElementsFromA

    I'd like to create a relation between ElementsFromA and Id (tblA). The key point here is to allow multiple values so a user could assign more than one, for example elements 1,3,4 (from tblA) for each ElementsFromA from tblB.
    Actually I've created a relation but I'm not sure if it's correct - there is no "+" sign in tblA datasheet view so I can't double check and confirm that elements are linked correctly.

    thank you so much for your help!

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I'm confused a little. Can you give us an idea of the out put your expecting in tableb.


    Sent from my iPhone using Tapatalk

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Right now, with the info you have provided, your question is very vague.
    I think you are asking about a 1 to many relationship, with the one being tblA and the many being tblB - but you need other fields in both tables.


    You might try reading
    https://www.techopedia.com/definitio...y-relationship
    http://ms-access-tips.blogspot.com/2...ationship.html

  4. #4
    siema24 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    22
    Hello,
    you're right my question is so vague. Sorry for that, let me describe the issue again. I'll also insert a few images just to clarify the situation.
    As I've already mentioned I have two tables A and B (I've changed table A values from 1,2,3,4 to a,b,c,d). I'm able to connect them with one-to-many relation so to each element from table B can be assigned to one or more elements from table A. Please check images below:

    tblA:
    Click image for larger version. 

Name:	tblA.png 
Views:	16 
Size:	12.2 KB 
ID:	27048
    tblB:
    Click image for larger version. 

Name:	tblB.png 
Views:	16 
Size:	7.7 KB 
ID:	27049
    As you can see I'm able to connect each element from tblB (1,2,3,5) with any ONE element from tblA (a,b,c,d). It is done with one-to-many relation and works just fine.
    Right now what I'm trying to do is to allow multiple values when creating relation so each element from tblB (1,2,3,5) can be assigned to ONE or MORE elements from tblA. Please check relations, tblA and tblB below:
    relations:
    Click image for larger version. 

Name:	relacje_docelowe.png 
Views:	16 
Size:	6.6 KB 
ID:	27050
    tblA:
    Click image for larger version. 

Name:	tblA.png 
Views:	16 
Size:	5.8 KB 
ID:	27051
    tblB:
    Click image for larger version. 

Name:	tblB.png 
Views:	16 
Size:	7.6 KB 
ID:	27052

    I've succeeded in creating relation that, in my view should be correct. I can assign multiple elements from A to each element B. Image "tblB" is a good confirmation - element "1" is assagined to a and b, element "2" - c,d, element "3" - a,b,c,d.
    Unfortunately when checking tblA I'm not able to verify whether element "a" is connected with elemnts 1 and 3 from tblB etc.

    Could you please advice If this relation is set properly? If so, how to confirm and list connections between elements from tblA with elements from tblB? Do I need some kind of a query?

    Thank you so much for your suport!

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Still not sure, but let me comment on what I see.

    The first set of images seem to display a 1-to-many relationship between tblA and tblB. The table property "Subdatasheet Name" is set to "Auto". This displays the "+" when looking at a table (tblA). (I run code to set this property to "[None]" for all tables.)

    ----
    The next set of images seems to indicate that "tblB" has a multi-value field. From what I have read, MS added this field type "to be more compatible with SharePoint".

    A multi-value field is actually a many-to-many relationship, but the specifics are hidden. (A many-to-many relationship is 3 tables.)
    See http://www.theregister.co.uk/2006/07...access/?page=1 (2 pages)


    I've succeeded in creating relation that, in my view should be correct. I can assign multiple elements from A to each element B. Image "tblB" is a good confirmation - element "1" is assagined to a and b, element "2" - c,d, element "3" - a,b,c,d.
    Unfortunately when checking tblA I'm not able to verify whether element "a" is connected with elemnts 1 and 3 from tblB etc.
    Because of the hidden table, you will not see the "+" in tblB that would allow you to see the related data.



    From what I've read over time, most experienced programmers avoid MVFs..... If you want/need to convert your Access BE to a major DB (SQL server, Oracle, dB2, etc), because of the MFV, you will end up redesigning your dB - non of the major dBs support the MVF data type.



    ----------------------------------------------------

    [begin personal opinion]
    If I created a quick and dirty dB for my personal use, I might (stress might) use a MVF.... but probably not.
    I never use Multi-value fields because they are such a major PITA to work with. I use the proper 3 table design when needing a many-to-many relationship.

    I also never use look-up FIELDS, nor calculated fields.
    [/end personal opinion] (sorry...not much of a rant - tired from shoveling snow)

  6. #6
    siema24 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    22
    Hello!
    I've gotten familiar with multi-value field table and I decided not to use them. I solved the issue by creating one more sigle-value field table. Thanks a lot!

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.......

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

Similar Threads

  1. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  2. Replies: 2
    Last Post: 09-04-2015, 06:46 AM
  3. Replacing many values at once in a multiple values checkbox field.
    By ConfusedDatabaseDesigner in forum Forms
    Replies: 2
    Last Post: 07-20-2012, 08:58 AM
  4. Problem with relations
    By PoorCadaver in forum Access
    Replies: 17
    Last Post: 10-18-2011, 12:31 PM
  5. Replies: 1
    Last Post: 04-14-2010, 08:02 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