Relation problem

[Sam]

I am stuck...

I am creating a database to track rehearsals and performances for musical groups. I want to create a report for each musician with only his rehearsal and performance dates. The problem I am having is that one person can be in multiple groups. My relationship is based on the group they are in. I have assigned each person to groups. (Example, Red Team)

My tables are:

Musician

Groups

Rehearsals

Performances

I want to create the report using the sub summery feature.

[comment]

Strictly speaking, you should have a join table between Musicians and Groups, since this is a many-to-many relationship. But in some cases you can get along by using a checkbox field in Musicians to assign a musician to multiple groups.

Also, it seems like Rehearsals and Performances could be rolled into a single Events table. Otherwise you will not be able produce a single report listing both types of events in a chronological order.

[Sam]

Is the purpose of the join file to house the dates of each team members performance and rehearsal schedule?

I would like to schedule the groups by inputing the date and assigning the group such as the red group. I do not want to have to schedule each person to a date? Is this possible?

[comment]

The purpose of the join file is to assign each person to a group, with each assignment being a separate record in the Assignments join table. Without a join table, you will not be able to produce a report like this:

Group A

• Adam

• Betty

Group B

• Betty

• Cecil

This is not really affecting your current problem. You CAN create group events - with the join table or without it. But if your events are split between two tables, you will not be able to produce a single chronological list of them - not for a group, and not for an individual.

[bcooney]

Here's an example. I'm having a problem with the musician report, though. If someone could see where I might have gone wrong with the data model, it would be appreciated.

I wanted to allow each musician to be a member of multiple groups. I also wanted multiple groups to be participants at an event.

I don't ever print portals like this. So, I'm thinking that the only way to create the musician schedule report is through calc fields set to value lists.

Events.fp7.zip

[comment]

It's not easy to understand the data model from looking at an anchor-buoy graph. Here's a simple ERD showing what the arrangement might be with two join tables. To print a person's schedule, you would GTRR from the person's record to the [color:red]Events table, and print it from there.

However, I am not at all convinced join tables are needed here. If events are regularly being scheduled for multiple groups - well, that's just another 'master group' that the members of the 'sub-groups' belong to.

Edited August 5, 2007 by Guest

[bcooney]

Your ERD is what I have: two join tables, Membership and Participants. I don't understand the "master group" idea.

It certainly is an easier model if a musician is a member of only one group.

I've tried basing the Musician's Schedule on the participants table, but that doesn't work either. It seems that there is no "child" table, since Participant only stores groupids and eventids and Membership only stores MusicianIDs and GroupIDs. Hmm.

[comment]

I don't understand the "master group" idea.

Think of one musician belonging to the following groups:

• Violins

• String Section

• Orchestra

If an event is scheduled for the String Section, it will be automatically related to all violinists, cellists and bass players. If it's scheduled for the Orchestra, it will be related to all musicians. No join tables are required for the purpose of producing each musician's schedule (though they might be required for other purposes).

Participant only stores groupids and eventids

True, but when you are in the Participant table, the info from Groups and Events is only a relationship away.

[comment]

Sorry, I made a mistake above: I wrote a person's schedule (when using a join table between Events and Groups) should be printed from the join table, when I meant from the Events table. Otherwise a person who is a member of two groups participating in the same event will get the same event twice on his/her schedule.

[Sam]

bcooney,

I too am having the same problem of creating a report for each musician of their rehearsals and performances. Your portal works, but I also do not like using a portal in a report. Any light on the subject would be appreciated.

[Fenton]

This is bcooney's file, with the addition of a printing layout. Instead of the portal, it uses Custom Functions, GetRows, to produce what looks just like the portal, but is a merge field, with tabs to separate the fields (which allows spacing on the layout). The idea is that the merge field will not get cut up when it crosses a page break like a portal tends to.

It still has glitches however, in that I couldn't figure out how to do a "conditional header" when the merge field breaks over a page (like: musician name "continued").

I haven't really followed the rest of the thread well enough to add anything else. But this will help with the portal printing problem anyway.

[P.S. The merge field has real tab stops, to align the fields. You can change them in layout mode, if you show the Text Ruler. Double-click the merge field to see. Requires some secretarial skill -]

Events.fp7.zip

[Sam]

Fenton, thanks for the Custom Function! I wish there was a way to report it as a sub summery.

[Fenton]

Thanks are due to Bruce Robertson, for the GetRows Custom Function, very useful. Though I wish there was a way to not have to "hard-code" the field names. Still, that's not really a problem, 'cause it's usually the last thing you implement, and the fields are not going to change.

You could do a Subsummary. But to do it like that for each musician I think you'd need another join table. You'd take the "events_Participants" table, and, using a script, break out each group into its multiple members, in a new table. This would be lots of records, but could be just IDs, and maybe dates.

Then you could do a Subsummary by Musician_ID.

One advantage of this would be, like I mentioned, the ability to do a "conditional header", for when a musician's records spilled over a page break. It was impossible (for me anyway) using the CF method from Musicians, because all the entries are in one calculation field.