Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43

    Adding New data to Composite table via multiple multiselect listboxes

    Click image for larger version. 

Name:	Capture.PNG 
Views:	37 
Size:	30.0 KB 
ID:	33104


    I have a DB as shown in figure. I have created a form which consists of multiple multiselect listboxes. The listboxes are 1)MAchine, 2)MAchine System, 3)MAchine sub system and 4)components. I have a text box and a button where in I create a new machine and send it to the Machine table. Now with this updated new machine in lb 1 I have the option of selecting whatever is existing from lb2, lb3 and lb 4. This new combination needs to be sent to the composite table i.e. Master Data table(see fig). The complexity is since it is many to many relationships the machine, or machine system or machine sub system or components might be repeated. How do I write a code for such complex environment?

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I think you would need to be more specific in your question. What is it you want your code to do?

    I don't see a many-to-many relationship in the figure you posted.

    https://www.youtube.com/results?sear...junction+table
    https://www.google.com/search?q=access+junction+table

  3. #3
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Okay let me start again. One machine has many machine systems, one machine system has many machine sub systems and one machine sub system has many components. Because of this ihave linked all four. But the other fields like failure modes, effects etc I have all put in one Master data table. Now if I want to create a new machine and add all existing Machine systems, Subsystems and components how will I do it?

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [edit] This post was embarrassingly wrong. deleting.... I hope no one saw that.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So if you now have 1 item of interest in lb1 and select 2 rows from lb2, you'd have to duplicate the value as another record from lb1 and pair each instance of lb1 with a different value from lb2. However, if you also choose 4 rows from lb3, which of the 2 values do you duplicate from lb2 to pair with the additional records required for lb3? Or are you intending to enter each into the same field, because I see you have one or more multi value fields in your table. Not many of us here would use mv fields. I for one wouldn't be able to help you do that since I won't use them (unless I was forced to) and have no idea as to how to accomplish that.

    If I'm off base here, then maybe more information about the desired result would be a good idea.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    No, there are no master ID in each of the 4 main tables. The master ID is only in the master data table. Only the components is linked to the tbl Master data(the link you have erased I think)

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What about this

    Click image for larger version. 

Name:	Untitled3.png 
Views:	34 
Size:	29.9 KB 
ID:	33106

  8. #8
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Yeah I know thats the problem. There are duplicate values being created in second, third and forth table. My intention is to just type in new machine name and it has to go and sit in the lb1 and table machine. And all the other subsequent levels should be linked to it. Like a tree structure.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Deepak.Doddagoudar View Post
    No, there are no master ID in each of the 4 main tables. The master ID is only in the master data table. Only the components is linked to the tbl Master data(the link you have erased I think)
    That was a silly error, disregard that post. See my post #7.

    Quote Originally Posted by Deepak.Doddagoudar View Post
    Yeah I know thats the problem. There are duplicate values being created in second, third and forth table. My intention is to just type in new machine name and it has to go and sit in the lb1 and table machine. And all the other subsequent levels should be linked to it. Like a tree structure.
    You stated in the OP that this was a many-to-many relationship, if I understand what your doing correctly then you are lacking a junction table for your many-to-many relationship.

  10. #10
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I think the master data table is a junction table.

  11. #11
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I will make it much simpler now and break it down part by part. When I add the new Machine via form, it goes and sits in the Machine table (with a new auto numbered unique ID) and also in lb1. No problem! This is happening!
    Now I want to associate this new machine to the existing machine system (from second table) values without going to the table to enter a new ID for the new machine in the machine system table with the field name Machine ID?
    If I were to do it manually I would go the the Machine table, see the new Machine autonumbered ID and enter it into the MAchine System ID to create the link between the them.

  12. #12
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Click image for larger version. 

Name:	Capture 1.PNG 
Views:	27 
Size:	20.6 KB 
ID:	33121
    I select the records from from and send it to the respective tables. But the ID's are not getting populated automatically.
    Click image for larger version. 

Name:	Machine.PNG 
Views:	29 
Size:	5.9 KB 
ID:	33122 Click image for larger version. 

Name:	Machine System.PNG 
Views:	32 
Size:	14.8 KB 
ID:	33123 Click image for larger version. 

Name:	Machine Sub System.PNG 
Views:	32 
Size:	24.9 KB 
ID:	33124

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Click image for larger version. 

Name:	Untitled4.png 
Views:	25 
Size:	32.9 KB 
ID:	33129

    Would you say a Machine has many MachineSystems and a MachineSystem has many Machines?

    Consider the following:
    Click image for larger version. 

Name:	ex1.png 
Views:	26 
Size:	9.1 KB 
ID:	33133
    Click image for larger version. 

Name:	ex2.png 
Views:	25 
Size:	24.8 KB 
ID:	33134

  14. #14
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Yes the Machine has many Machine System. MAchine System has many MAchine sub System and so on. Duplicating the data is okay for us. We will create a New Machine and its ID has to now be populated in table MAchine iD of the MAchine System table.
    Click image for larger version. 

Name:	machine new.PNG 
Views:	31 
Size:	5.9 KB 
ID:	33135
    Similarly the MAchine System ID 15 from the table Machine System should be populated in the Machine system ID field of the table MAchine subsystem for the MAchine Subsystem ID 42 and 43.

  15. #15
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I am aware of this. I am okay with the present method since again the four tables are related to a huge Master data table. Duplicating data is okay with us. For this existing data structure is it possible to meet the requirement for new machine creation?

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

Similar Threads

  1. Multiselect Multiple Listboxes
    By Deepak.Doddagoudar in forum Forms
    Replies: 29
    Last Post: 03-15-2018, 03:26 PM
  2. Replies: 11
    Last Post: 02-02-2017, 10:31 AM
  3. Adding Records from MultiSelect ListBox
    By Voodeux2014 in forum Forms
    Replies: 3
    Last Post: 10-16-2015, 09:22 AM
  4. Replies: 5
    Last Post: 02-05-2013, 01:18 PM
  5. Combining data from multiple listboxes
    By dshillington in forum Access
    Replies: 1
    Last Post: 10-04-2012, 11:53 AM

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