Jump to content

Relation problem


Sam

This topic is 6156 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Schedule.png

Edited by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6156 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.