Lost in relationships...

[Steve G]

I've got a reasonably good beginner/intermediate grasp of relationships but I'm now in a position where I have not one, but TWO separate databases (with no relation to one another by any definition) that have the same problem, and I'm stumped.

I'm creating a database (or trying to) for a friend of mine who runs an outdoor art show production business. There are three main tables; "Shows" which contains information of each show (title, location, etc), "Exhibitors" which contains information of each exhibitor (business name, etc), and "Payments" which contains information of each payment he's received (check number, etc).

There are also a few "link" tables that are used to establish relationships between tables. "Exhibitors to Shows" lets me link exhibitors to shows, for example. Using this I've set up a portal on the "Shows" table that lists the exhibitors that are attending any given show, and a portal on the "Exhibitors" table that lists the shows that a given exhibitor has attended. This all works fine.

What I'm stuck on is the payments part. Unfortunately it's not a simple relationship between vendor and payment, or between show and payment. Any given vendor might send multiple payments for a single show, or a single payment for multiple shows. Rather than a simple 1:1 relationship (i.e., exhibitor to show) I've got a multiple relationship. I need to track not only what payment has come from what exhibitor, but also for which show.

On top of that, I need a way to show information from multiple tables in a single portal. For example, a portal in the Shows page would list the payment status for that show; each line would contain the exhibitor name, amount owed for the show, the payment date of a given check, the check number of that check, the amount of that check, and the balance due *for that show*. (Example: An exhibitor may owe $300 to exhibit at a given show and has sent three checks for $100 each. Each of those three payments would be shown in the portal, but the "Balance Due" in all three of them would be $0. Alternately, an exhibitor may owe $100 for one show and $200 for another show. The balance due for that exhibitor in the "show attendance" portal should reflect the balance due for each separate show.)

If I can get this one running I'm reasonably sure I can apply the same fix to my other database. Any help would be appreciated.

[comment]

How do you plan to do the allocation of payments to charges? Say someone owes $100 + $75 + $50, and they send a check for $150. Will your friend split this manually, or should it be done automatically, and if so, by what rules?

[Steve G]

Because such a situation happens very rarely (twice in the past three years) it will be done manually. Extending your example, they can apply that check to multiple balances. They'd apply check #xxx for $100 and the same check #xxx for $50, thus having two entries in the "Payment" table for the same payment. I can create a self-referencing relationship from the "Payment" table and plop it in a portal so that split payments like that can be seen together.

[comment]

Well, then perhaps it could be simple: