Yes, that does sound complicated, but I think you have the right idea. What you described is exactly what you need. Every many-to-many relationship and every self-join where there are multiple joins will require a junction table. I would just lay it all out maybe in Excel, and that would be the best place to start. Go with that first, and then if you want to post a screenshot, I'll look it over, and we'll see if you're on the right track. But it sounds like you know what you're doing.

Thank you! I will try the Excel Spreadsheet idea. When i speak to the guy I am doing this for, and when I look at the data - each ITEM has multiple ITEMS its related to and each of those items (like an address, or project or other surveys) can have more than one. And the addresses can have different surveys and/or projects related to those.  It's basically a web. I am really glad I purchased your relationship seminar.  Its already helping! Ill post the spreadsheet after I complete it.  People at my work are seeing what I can do and now Im being asked to do special projects like this. I am SO glad I found your website. THANK YOU!

I'm just glad to be of service. Yeah, that can be pretty crazy, but just think of it this way: everything that has to be attached to multiple other things - even things of the same type, like people to people - each many-to-many relationship needs a junction table. Once you know that, then it's pretty easy to put together.

I had a crazy database that I built for a lawyer once. He had clients that could be attached to cases that were attached to multiple other law firms. All of those things had to be many-to-many relationships because:

- Customers could be attached to multiple cases
- Cases could be attached to multiple customers
- Both of those could be attached to different law firms

So it was a crazy web like you're talking about, but I managed to get it done. It's all just many-to-many relationships and lots of subforms.

And then don't get me started on tracking billable hours for all of those! LOL

Self joins can be tricky. Inherently they only allow a one-to-one relationship. To get around that, you have to start adding multiple fields with a specific purpose super-imposed upon the fields. Think of a family, each son and daughter can have only one biological father. But the father may have multiple sons and multiple daughters (do we really want to add son1, son2, son3, etc. to the father record? No!). This is when you need to get into the many-to-many scenario, especially when you add a mother for the child.

In my demos of the ADS, I show how Marilyn Monroe had two previous names, each with a self-join going to a previous Entity. Fred Flintstone however has many relationships, and each of those entities can have their own relationships. So, this is doing a "self-join" through a many-to-many table. Likewise, for two different tables, a user can belong to many user groups while each user group can have many users. An example that isn't a self-join, but is many-to-many.

It sounds like you have both scenarios happening at the same time with your tables. At its simplest, you have a project and survey tables, as well as three many-to many tables for project<->project, survey<->survey, project <-> survey.

Thomas Thanks for reminding me about the Project-Project self-join. I made an Excel Spreadsheet laying it all out but forgot that one part. I posted screenshots above.

Richard Thank you for this. You are so right.  Once I started laying it all out in the Spreadsheet - and listened to you r relationship seminar where you said "If anything has two or more of it you need a separate table for that - it started making more sense and got a whole lot easier. I've posted my screenshots of the spreadsheet above.

I realized I had to make a JUNCTION Table for the Actual Location (County, Township, Municipality) so I added all those tables. I also moved a few of the items around. Blue highlights are table names and green are auto numbers. I am also making a query for the SiteLocation so that when a county is chosen, the township will autofill, etc. And that info will be saved in the SiteLocationT Junction Table with the PinID or the SiteID or both. (This is from the multi-combobox drop down tutorials).

Well, I don't necessarily understand all your terms because I know very little about archaeology (aside from the documentaries I've watched - oh and Raiders of the Lost Ark). But as far as I can tell, everything looks good.

I might blow your mind by suggesting some of these things could be put into helper tables. For example, your county, township, and municipality could technically all be in the same table, but that might add unnecessary complication to what you've got going on there. So if you're more comfortable just leaving it as is now until you get it built, and then later on if you decide you want to do that, then that might streamline things for you.

But as far as I can see, everything looks good. See... laying stuff out in spreadsheets is definitely a great first step.