All payments, not specific ones.

[Steve G]

I was SO close to finishing this database. That is, of course, where I slammed into a wall.

Relevant part of my relationship table:

On a SHOWS layout I have a portal that calls to the PAYMENTS table. I use this to show the payments received for that particular show; the portal that displays the payment list includes information from the LINKS EXHIBITORS TO SHOWS and PAYMENTS tables. This works well with one BIG exception: If a given exhibitor is assigned to two different shows, ALL of that exhibitor's payments (not just those for that particular show) appear within the portal list.

The conditions for this is somehow keyed to show attendance; if Exhibitor A is assigned only one show, then Exhibitor A's payments only appear in the portal for that one show. But if Exhibitor A is assigned two or more shows, then Exhibitor A's payments appear in those two or more shows (but not in any shows where Exhibitor A is not assigned). I'm sure this is a relationship that I screwed up somewhere. I thought that the relationship I have between SHOWS and LINKS EXHIBITORS TO SHOWS would handle this but obviously I've missed something.

How can I get my portal to show only payments that are assigned to that specific show?

[Anuviel]

Um, sounds like a relationship issue but it might not be. Got lost reading your post, I get lost a lot , but a how do you relate your exhibitor to the show?

Do you keep it as one exhibitor can have many shows or one show can have many exhibitors? Reason I am asking is, I believe, if you are using, for example, same exhibitor ID for different shows when you assign an exhibitor to a show, if your relationship from a show to exhibitor is that ID then all of the shows will show in a portal on exhibitor layout and to get only particular ones you will have to filter the relationships.

So if you assign an exhibitor to a show you could have key which would be specific to that one show and could create a relationship between that show and that particular exhibitor based on that key so when you put a portal in that particular show from a relationship based on that key only the payments from that exhibitor for that show would appear as exhibitors would have an unique key that relates them to that show.

*Just read my post, got confused myself, sorry, tired, hard day at work.

Edited October 16, 2008 by Guest
Added *

[Steve G]

Not a problem; it's a confusing situation. Here's how the process works.

There are three main tables; EXHIBITORS (containing the names and details of all the exhibitors), SHOWS (containing the names and details of all the shows), and PAYMENTS (containing the details of all the payments received). There's also a "LINKS EXHIBITORS TO SHOWS" table (hence, LETS) which is 'hidden' and is used as a means of connecting information.

To assign an exhibitor to a show, we go to the SHOWS table and, via a portal (to the LETS table), add an exhibitor to the show. This creates a new record on the LETS table that contains the exhibitor's name, exhibitor's ID (his record number in the EXHIBITORS table), and show-specific information such as how big a booth is requested, if he wants a corner booth, etc). The relationship here is between the SHOWS table and the LETS table (using the EXHIBITORS table for exhibitor information - name, etc).

Now the exhibitor is assigned to a show. Next, we receive a payment from the exhibitor for that show. We go to the PAYMENTS table and create a new record. First we select an exhibitor; this is done via a pop-up menu that contains only a list of exhibitors who are assigned to a show. This value is stored within the PAYMENTS table and contains the exhibitor's ID number. Once the exhibitor is selected we select a show; this is also done via a pop-up menu that shows only the shows that the exhibitor is assigned to. This value is stored within the PAYMENTS table and contains the ID number of the record within the LETS table, thus linking the payment to the exhibitor and show.

In theory this sounded perfect, and in practice it works great - until I have an exhibitor assigned to more than one show, at which point all payments from that exhibitor appear in all shows that the exhibitor is assigned to, regardless of whether or not the payment is assigned to that show. That's where I'm stumped.

Hope that makes more sense. :)

[comment]

The Payments table should be a child of the LETS join table (why not call it simply Booths, or Registrations?), not of Exhibitors.

[Steve G]

Turns out that was only part of the answer. I had to create a lookup field in the PAYMENTS table, then associate a new instance of the PAYMENTS table to the LETS FIELD via that lookup field.

I called it LETS rather than Booths or Registrations because I wanted it to be descriptive of its task. It isn't simply a Booths table or a Registrations table; I have other things going on in there as well, but 90% of its function is to link exhibitors to shows. Hence, LETS.

[comment]

I don't see what you would need to lookup (and from where) for this. Once you have assigned a payment to an exhibitor and to a show, it can be linked to a record in the LETS table using these two fields (assuming there is only one exhibitor/show combination allowed in LETS).