Counting No of Records in a Portal

[stupididity]

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?

 

 

[comment]

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.

[stupididity]

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.

 

[comment]

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

 

Which specific value would that be?

[stupididity]

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.

[comment]

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.