Jump to content
Sign in to follow this  
csharpmin

joining to a self-join

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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