We have three database files currently to track mediations. The first is a Case database file with all information related to cases. The second is a Mediators database file with mediator contact information, etc. The third is a join file (I think) with a record for each time a mediator shows up in a specific case from the Case file. There can be up to 3 mediators per case.

Okay, the Case file is set up with a portal to the join file so that we can enter a mediator for the specific case and a record is automatically created in the join file. This works fine except for one thing. Each case may have up to 4 mediation sessions.

I'm trying to find a solution so that we can enter up to three mediators per session and have separate records in the original join file so that we can track the number of hours each mediator spends.

Am I making sense?

I tried creating a fourth file which has separate records for each session. In other words the Case file portals to a Session file which portals to the original hours join file. A one to many to many chain. It doesn't work (surprise) and I wonder if I'm barking up the wrong tree or just have my relationships set up incorrectly or...?

Thanks for any help you might be able to provide.

Barry,

St. Paul, MN

quote:

---

Originally posted by brmadore:

We have three database files currently to track mediations. The first is a Case database file with all information related to cases. The second is a Mediators database file with mediator contact information, etc. The third is a join file (I think) with a record for each time a mediator shows up in a specific case from the Case file. There can be up to 3 mediators per case.

Okay, the Case file is set up with a portal to the join file so that we can enter a mediator for the specific case and a record is automatically created in the join file. This works fine except for one thing. Each case may have up to 4 mediation sessions.

I'm trying to find a solution so that we can enter up to three mediators per session and have separate records in the original join file so that we can track the number of hours each mediator spends.

Am I making sense?

I tried creating a fourth file which has separate records for each session. In other words the Case file portals to a Session file which portals to the original hours join file. A one to many to many chain. It doesn't work (surprise) and I wonder if I'm barking up the wrong tree or just have my relationships set up incorrectly or...?

---

You have Cases, Mediations and Mediators. The Case -> Mediation join is a simple one-to-many join. The Mediation <--> Mediator is a many-to-many join and requires the Session (or whatever you want to call it) join file. If you look at the setup in this way you should be ok.

You were trying to work 4 databases into your many-to-many join, which does not work.

I am sure that you can setup the proper joins between the Case and Mediation file. Make sure that you give the Mediation file some kind of interface so that you can effectively manipulate the many-many join to the Mediator file.

I am sure that you can setup the proper joins between the Mediation and Mediator files as well via the Session join file.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=