M:M->1:M design

[ledgerr.rob]

Good afternoon~
Access 2007, windows vista

I'm trying to understand how i can get nested subforms to work. I have a main form with a subform that is based on a many to many relationship. This subform is working. I would now like to nest another subform that would be based on a one to many relationship.

tblJob M:M tblReferences 1:M tblLetters

Each job could have any number of references but only one letter could be selected for each reference.

I think of cascading combo boxes and of nested subforms but have had no luck with these. I have tried everything i know and have done a lot of searching but am at a loss. I'm starting to think my relationships and design are flawed. I would very much appreciate any suggestions.

Thank you


My apologies if this belongs in a different forum such as the Forms forum.

 

[jdraw]

Do you have your tables and relationships set up? Could you post a jpg?

You may need to break your M:M into two 1:M

Just thinking and typing:

Job--->JobRef<---Reference------>LetterReference<-----Letter

 

[ledgerr.rob]

jdraw
I've attached a jpg of my relationships.

Thanks,

 

[jdraw]

Your structure seems fine.
Tell us about your forms.
Form/subform is 1:M generally.

 

[ledgerr.rob]

ok.:

My main form's (frmJob) recordsource is tblJob. My subform is based on the many to many relationship between tblJob and tblReference. So i created a query that includes both tblJunction and tblReference. In the query i include JobID and RefID from tblJunction and then FirstName, LastName, and Phone Number from tblRefernce. I saved this query.

I opened frmJob in design view, dragged and dropped the saved query onto it to create the subform. I then changed the RefID textbox to a combo box and made it's row source tblReference.

My thought is to have the user be able to enter/edit jobs but to just be able to select available references already entered on a different form. The separate form used for this uses main form frmReference and subformLetter for assigning the appropriate letters.

I think this covers the forms and some of my process in creating them...I appreciate your help in this.

 

[jdraw]

I mocked up some tables based on your model, and populated same with a few records.
Created a query to deal with the many References and letter referring to jobs.

I created a form (Main) and a subform (data from the query is the record source), and a form called frmLetter.

You can open form Main and see the data in the subform, you can iterate through the records on Main.
You should adjust the field widths in the subform so you see all the fields,-- it's key that you see LetterId.

When you see a Reference and LetterId for a Job, you can click on the LetterId in the subform, and a new form (frmLetter) opens showing the appropriate letter record.

Don't know if that helps, but it was a shot on a Friday night.
Hope it's helpful.

Note there are no references for JobId 5.
Good luck,

 

[ledgerr.rob]

jdraw

this is great! I really appreciate how much you've helped. I was looking at the sample you provided and I noticed something. The MAIN forms' subform shows every letter by the selected references. This is very close but I think someone would only submit one reference letter from any one particular reference. They might use 3 references but only one letter per reference per job....Understandably you were helping me with this together on a friday afternoon .

I just wanted to mention it. I'm going to work through some ideas with your provided sample tonight and the weekend. If you happen to think of something else I'd love to hear. I'm going to focus on nesting a subform. I'll post any results i come across.

thanks again
rob

 

[jdraw]

I just dreamt up the sample data. I have not put any restrictions on the number of letters per reference.
If a reference can only be associated with 1 letter, then change the test data to reflect your "business rules".

The Main form's subform is showing all references and all letters based on the JobID. That's based on the relationships.The samples were meant for concepts only.

What exactly is the issue?What would you like to see?

Can you make a picture or a sample of the form setup you'd like to have?

 

[ledgerr.rob]

Thanks for your reply. I hope I didn't seem ungrateful for your help. Quite the contrary.

Reading your post I think my problem is understanding how to put the restrictions on the data to limit to one letter per reference per job. Would the restriction be based off modifying the relationships?

I've attached a .jpg of the sample data that you provided in the way that my minds eye sees it... For JobID 1, there are RefID's 1, 2, 4 assigned. Then only one letter is assigned for the job's reference: LetterID 1, 4, 7.

I feel that my lack of understanding of database structure, manipulation, etc... is limiting my ability to properly communicate my goals and struggles to the forum. My apologies. If there is anything else i can provide, please let me know.

I appreciate all of the help!!
rob

 

[jdraw]

So the rule is only 1 Letter per Reference per Job. I have modified the database. Relationships are changed. If you look at the table designs, check the indexes and look at the Unique indexes.

A Unique compound index is made up of 2 or more fields. It prevents duplicate entries based on the fields involved. I was trying to sort out how to only allow 1 Ref 1 Letter per JobId. The change in relationships and unique compound indexes does it (I think).

I may have changed some of the Ref and Letter assignments, and Job to RefLetters.
Check the values in the tables against your expectations.

see the attached jpg for the UniqueIndex - there are some on other tables.

This database has been altered from previous posts and compacted before posting.
Try adding some Ref/Letter combinations and relating them to Jobs. See if it will allow duplicate Ref/Letters for a Job and see the message.

Good luck.

 

[ledgerr.rob]

thanks so much. I've been away for a few days now and just getting back into things. I'll take a look at your example.'Unique compound indexes' are new to me. I'll be looking into those. I'll get back on when i've taken a look at things.

Thanks again!!!