Jump to content

how to find records with no related records?


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

Recommended Posts

I'm trying to create a report that shows mee all Tutors that are not tutoring a course.

The tables involved are:

Tutor --> TutorCourse --> Course

Where TutorCourse is a join table.

 

It's easy to see which tutors have courses as that is prcisely what is stored in the join table.

 

The approach seems to be to interate through the Tutor IDs in the Tutor table and see which one's do not have a relation to TutorCourse... but I don't know how to do this.

 

Is this even a good approach??

 

Jason

Link to comment
Share on other sites

From Tutors, perform a find in your TutorCourse table.  

 

It would look like this:

 

Enter Find Mode [ uncheck pause ]

Set Field [ TutorCourse::TutorID ; "*" ]

Set Error Capture [ On ]

Perform Find [ ]

#  this finds all Tutors with courses assigned

Show Omitted

Link to comment
Share on other sites

  • Newbies

LaRetta's method is most efficient.  You could also create a field at in the Tutor table that counts the number of courses which would probably be helpful in determining how to spread things out.  You could also then find and sort on this field from there on.

Link to comment
Share on other sites

You could also create a field at in the Tutor table that counts the number of courses which would probably be helpful in determining how to spread things out.  You could also then find and sort on this field from there on.

 

I should mention that a calculation in Tutors which counts a related table would be an aggregate and unstored.  If you need to simply display the counts from a related table then that is fine but It would be VERY inefficient to then sort or find on this aggregate calculation in Tutors.  Always avoid searching or finding on unstored calculations.  Searching a related ID is searching a stored field.

Link to comment
Share on other sites

This topic is 3597 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.