Another relationship question

[taw]

Hello,

Trying to work out a many-to-many relationship that has multiple fields in one table relating to records in another table. I am building a form that will keep track of several manufacturing lines on a daily basis. The information needs to be grouped by day. I am keeping track of different materials used on each line. So on any given day, a specific material might be used on line 1 only, or it might be used on lines 4 and 8 or whatever combination.

To oversimplify the setup, I would have a production table and a material table. The production table would have one field for each manufacturing line that would relate to the material table which would contain the details of each material.

I have done this type of thing before completely programatically and ingored setting up the relationships, but I keep thinking that there has to be a way to do it the right way. All of the examples I can find for many-to-many are for just one field.

Hope this makes sense. Thanks for any help

 

[Fizzio]

To create a many-many join, you will need a junction table. Try this example (tables and relationships only) to get an Idea of how it is acheived. Try building forms around this example to see how the relationships work.

 

[taw]

Thanks. I can see just by looking at you're example that if I put the production lines into another table that it will work. I knew I was missing something simple.

Thanks again.

Tom

 

[Pat Hartman]

Here's an example with forms.

 

[jon98548]

Now I have a question. I notice in both of your examples that in the junction table all fields are PK. Why? My first thought is to make all fields required. So if you book a venue, you must fill in the date. Am I on the right track or in the weeds as usual?

 

[Fizzio]

Jon, in both our examples, we use a multipart primary key in the junction table to avoid duplication of data.
As it happens, we have both used a date field as a part of the primary key.

In plain english (in Pat's Example), the relationship says that on a given date, a given customer may only book a venue once for that date.

It allows a venue to be booked out on the same date by many customers and also a customer to book many venues on the same date.

You can lock this down further if you needed to by using time for example rather than date for room availability. In this case you would only need a dual field primary key, TimeSlot and Venue. This would ensure that that venue was only booked once for a given time slot but not dependent on the actual customer.

Does this clarify things a bit?

 

[taw]

Thanks also for that one Pat. I had downloaded it before my first question, but didn't quite see what I was needing to do. I didn't have the lines broken into another table and that will fix the problem if I do. Now my problem is to make it all appear the way the customer wants. There are 8 production lines and they want them displayed on an input form in colums and then print out a 1 page report in the same format. There would be one report per day. This is why I had all 8 lines grouped in one table with the date as the key. It makes for an easy form and report, but doesn't give me the relationships I need for queries.

 

[jon98548]

Absolutely, Fizz, thanks. I haven't had the need for that yet, but was thinking that was the purpose. Always seeking knowledge. Hmm ...