Can assets belong to both contracts and assets? Can an asset be on multiple contracts/liens? If so, it's not a 1-M relationship. It's M-M.

No. Each asset will only have one contract, and one lien.

Can the same asset be assigned to a contract AND a lien?

Yes. So we are an in-house finance company for a truck dealership. We loan money to customers for the purchase of a truck, and then borrow money from a much larger lender for that truck. So say I have Truck A. I'm going to sell that to a customer under Contract A, and then borrow money from another lender under Lien A. To complicate things even more, I need to keep track of insurance policies for each asset as well. With another one to many relationship. One policy will have many assets.

So each asset will have a contract, a lien, and an insurance policy.

So a contract can have multiple assets? In other words, you can list multiple trucks on the same contract, correct? So if Joe Smith buys 3 trucks, they could potentially all be on one contract. Correct?

Now a lien can have a completely different set of assets assigned to it, right? So if you took 10 trucks from Joe Smith and a few other clients and got a single loan to purchase those, that could all be on the same lien.

Am I correct so far?

If an asset can only have ONE contract, ONE lien, and ONE insurance policy, now we're talking one-to-one relationships.

You are correct. A contract could have say 3 trucks. Some banks we borrow from may have a batch of 50 trucks on one lien. While other liens match up with the contract and have just the 3 trucks. So it's definitely not a one-to-one.

OK, now going the other way, an asset will be on one AND ONLY ONE contract, right? A truck would only have one contract.

Could an asset have more than one lien? Might there be a truck that was covered on multiple loans? I don't think so, but I need to be sure.

So if this is the case, you'd have 1-M relationships between contracts and assets, and then liens and assets, like you initially said. I'd also set up insurance as a 1-M in case a truck has multiple policies associated with it.

Lenders to liens would also be 1-M.

I would make a form where the Contract is the parent record, and then have a subform listing all of the assets on that contract. Do the same thing for liens.

I would also then make a query where you have each asset and you could link to the contract and the lien that it's on (since each asset can only have one contract, and one lien). This would then make it easy to find that data. Put this into a continuous form and you could add easy sorting and filtering too.

You could set up double-click events to jump from one to the other. So if you're looking at your asset continuous form, double-click on the contract field to open that form, and it will show you the rest of the trucks on that contract. You could do a lot with this. :)

Okay. I will work on this. I started with your beginner series and am halfway through expert. Next up is advanced, and then hopefully VBA because I also need to create amortizations. We have a database that is absolute garbage... so because I like to make things difficult for myself I've decided to just build my own. I appreciate all of your help.

Access Amortization Seminar
This might really help you 😉

Thanks! That seminar is my goal. I'm not quite to that level yet.

>> Each asset will only have one contract, and one lien.

That is not exactly true.  Each asset (for instance, a real estate property at a specific location) can be involved in different contracts, different liens, etc., after it has been sold, bought, foreclosed, sold again, etc., over a period of time.  So this is essentially a many-to-many relationship -- one contract can involve multiple assets, and one asset can be involved in multiple contracts.  Most real-life examples are like that.  True one-to-many relationships rarely exist.

Regarding your original question of how to choose from the "many" side to look up items on the other "many" side, a combo box can be used to look up the first "many" side, and the combo box entry is then used as criteria to get the second "many" side items.  See picture below from one of my apps.  A combo box shows a list of orders.  The selected order will populate the form.  The seems more like an issue with user interface design.

It's always important to look at how the specific customer's business works versus how other businesses may work. If her business is only ever involved with one transaction per asset, then it's fine to build the database that way.

But my experience has shown that it is all pretty "organic."  For instance, an order form can let you enter an order number to look up the detail of that order (product numbers, units, etc.).  But with the same data, you can also make a form that lets you do the reverse: enter a product number to search for all order numbers that have that product.  This is essentially a many-to-many relationship.  And I think most businesses deal with situations like this in one way or another.

At the end of day, world there be any reason to build a one to many over a many to many? Unless there is a specific need for a one to many, might as well build the many to many from the start IMO 🤠

Well, aside from the obvious that building many-to-many relationships involve more work and are more complex than one-to-many relationships, if you absolutely, positively will never need the many-to-many, then I say just make it one-to-one. On the other hand, if you think that you might ever in the future need the many-to-many, then put it in there.

Prime example: I had a customer like this years ago that did vehicle loans, and it was always exactly one vehicle for a loan; never more than one. They would never put multiple vehicles on the same loan, and nine times out of 10 when they had a customer, they never saw that customer again because they would then farm off the loan to a bigger bank. So, they didn't really need to worry about multiple loans for the same customer, but I talked them into it just in case.

So it's really more about how the customer's business model works than anything else.