One-to-many-to-many relationship in a form?

[Bazsl]

I am very new to Access although I do have relational database and programming experience in a past life. I need to create a form that models a 1-to-many-to-many relationship and I can't see how to do that since the access form controls do not have both a DataSet and Data Field property. This is such a basic thing I can't believe that Access does not support it. Can someone give me some guidance? Thanks.

[ranman256]

use the form wizard to make a master /child form.
the master form will have a single record of the master table (1)
the child subform can show may records of the child table. (many)

[Bazsl]

Thanks for the quick reply. Not a 1-to-many relationship. I need to show a 1-to-many-to-many relationship; three tables. In this case Customers have many Events and each Event has many Costumes.

[orange]

This is such a basic thing I can't believe that Access does not support it. Can someone give me some guidance?

More info needed. Please provide a scenario and/or sample data to show the business rule(s) involved.
Can you post the data model supporting this construct?

[Bazsl]

Orange, Sorry, I thought I had. It is a 1-to-many-to-many relationship between the Customer, Event and Checkout tables. One customer can have many events. Each event can have many costumes checked out. I don't understand what business rules have to do with the referential integrity relationships. Thanks.

[orange]

Perhaps you can elaborate on the description. In simple terms, give us an example that pulls the pieces together in business terms.
Tables: Customer, Event and Checkout
Where does "costumes" fit?

From my reading of your posts, it seems analogous to a library:

A Customer may borrow many books.
A Customer may make several "borrows" over a period of time.
And the situation where a customer had may visits(borrows) and each "borrow" involved 1 or more books.

I don't understand what business rules have to do with the referential integrity relationships.

More info on business rules.

Good luck with your project.

[Bazsl]

If you like the library analogy then the tables are City, Library and Checkout. Each city has many libraries and each library has many checkouts. If an ER diagram would help: City-->Library-->Checkout or Customer-->Event-->Checkout. The purpose of the form is to allow the user to select a customer then select one of the customer's events then insert checkout records for that event.

[orange]

I was trying to find out how your tables related in a business context. However, here's a link to a library data model that can have many libraries in many cities.
http://databaseanswers.org/data_mode...ooks/index.htm


Here is a more basic library model
http://databaseanswers.org/data_mode...rary/index.htm

[Bazsl]

I understand how to design relational databases and draw ER diagrams. I did both for many years before retiring. What I do not understand is how to show the three related tables in a single Access form. What I need is a master/detail form that also has a detail of the detail, if that makes sense. In other words a master/detail/subdetail form. Is there a way to create such a form in Access?

[orange]

Here is an older video set from datapig that may help with form and multiple subforms.
It's done in AC2003 but concept is same for other versions.

http://www.datapigtechnologies.com/f...subforms1.html
http://www.datapigtechnologies.com/f.../subform2.html

Good luck.

[Bazsl]

I'll watch them. Thanks.

[orange]

That Datapig reference has been used for many years.
I found some newer ones via Google, but I have not watched them.
You may also find these helpful.

https://www.youtube.com/watch?v=r-Z_nhBtJCI
https://www.youtube.com/watch?v=8BZoR7JaKsY

[Bazsl]

Thanks very much.