I would appreciate a little help as my head is getting sore from banging it!

 

I have a 4 tables 

• Students - pkStudentID, also contains name address etc
• Classes - pkClassID, also date etc
• SC_JoinTable - fkStudentID, fkClassID, fkCarnetID, payment method, payment amount

so far they are working ok, and I can have students attending many classes, as well as each class with multiple students.

 

Where I get stuck is how to structure my last table.

• Carnets - pkCarnetID, fkStudentID

A carnet is a bulk purchase of Classes, in this instance of 6 classes (although that may change in the future) 

 

What I want to do is have a CarnetStatusField that switches to "inactive" once the student has attended all 6 (or Xnumber) classes.
I have a portal that shows the Classes::Date, etc...

where        SC_JoinTable::fkClassID = Classes::pkClassID

and            SC_JoinTable::fkCarnetID = Carnets::pkCarnetID

 

I can't seem to find a way to count the number of records in the portal.  Am I missing something simple or is my logic crazy?

 

 

Perhaps I am missing something, but I see a fkCarnetID in the join table - so assuming a relationship:

 

Carnets::pkCarnetID = SC_JoinTable::fkCarnetID

 

each record in the Carnets table can count the "used up" registrations simply by =

Count ( SC_JoinTable::fkCarnetID )

--

 

I have a portal that shows the Classes::Date, etc...

where        SC_JoinTable::fkClassID = Classes::pkClassID

and            SC_JoinTable::fkCarnetID = Carnets::pkCarnetID

 

 

I am afraid that makes no sense: you can't have matchfields from two different tables on any side of the relationship.

Thank you for your reply.  

 

 

Perhaps I am missing something, but I see a fkCarnetID in the join table - so assuming a relationship:

 

Carnets::pkCarnetID = SC_JoinTable::fkCarnetID

 

each record in the Carnets table can count the "used up" registrations simply by =

Count ( SC_JoinTable::fkCarnetID )

 

 

This counts all records with "any" value in the SC_JoinTable::fkCarnetID field.

 

I need to count all records with a "specific" value in said field.

 

I need to count all records with a "specific" value in said field.

 

Which specific value would that be?

The specific value is the pkCarnetID field value.

 

I have now managed through a fairly long winded script using the get(FoundCount) function.

 

Thank you for nudging me back on track and away from my crazy logic.

This counts all records with "any" value in the SC_JoinTable::fkCarnetID field.

 

I need to count all records with a "specific" value in said field.

 

 

Which specific value would that be?

 

 

The specific value is the pkCarnetID field value.

 

 

I don't think you have tried this, have you? Because if you had, you would have seen that it counts only related records. An related records, by definition, are those where SC_JoinTable::fkCarnetID = Carnets::pkCarnetID.