joining to a self-join

[csharpmin]

I'm working on a database for my babysitting business. I'm having a problem showing certain data in a portal, and I think it might be because of relationship problem.

I'm working with four basic tables: Sitters, Clients, Jobs, and SitterAvailability. On the job layout I have two portals. One is a self-join that counts the number of jobs that the client and sitter from the selected job have worked together. The other shows the sitters that are available for the current job. I want the availibility portal to count the number of jobs between each available sitter and the client from the selected job. I've tried all sorts of table occurance configurations and can't come up with anything. I want to be able to sort my list of available sitters by the number of times they've sitted for the client.

I've included a bare-bones sample of my sitter database. The problem field is highlighted in yellow. Any help would be greatly appreciated.

Sitters_test_3.zip

[Fenton]

It's a little bit of a tricky one. Because you require both the Client and the Sitter for this combo, but only the Sitter is available locally in Sitters Available. The solution is to go get the Client ID from the Job, where it exists and is logically available from Job_Sitters_Available; since a job only has 1 client and it's already entered.

You get this by creating an unstored calculation in the Sitters_Available table. Notice that the calculation must be defined to evaluate IN the Job_Sitters_Available table occurrence (choice at top of calculation dialog; not the anchor TO of Sitters_Available).

This is a somewhat rare occurrence when data "tunneling" is still useful.

Then you can create a relationship to Job, from Job_Sitters_Avail, based on both SitterID AND _cClientID. Then you can count the combo.

Sitters_test_4.fp7.zip