I have two tables, WorkshopMaster and WorkshopDetail. On the Master List Page I'd like to count the number of enrolled students for each workshop.
These workshops are mostly for married couples, but sometimes only one of the couple can show up. Each record in the WorkshopDetail has a field for Male's Name and Female's Name. When only one of the couple shows up, we just enter that info in the approriate field, leaving the other name field blank.
I figured out how to add a field in PHPRunner on the Query tab for the Count() for both these fields. Like this:
SELECT
WorkshopMaster.ID,
WorkshopMaster.`Workshop Fac ID`,
WorkshopMaster.`Workshop Date`,
Count(WorkshopDetail.`Male's Name`) + Count(WorkshopDetail.`Female's Name`) AS Enrolled
FROM WorkshopMaster
INNER JOIN WorkshopDetail ON WorkshopMaster.ID = WorkshopDetail.`Workshop ID`
GROUP BY WorkshopMaster.ID
But the count I'm getting is wrong because when you submit the blank fields, they are no longer NULL. Which I've tested by setting those blank fields to NULL using phpmyadmin, which does give the correct count, but everytime a new record is added with a blank name field, the count gets thrown off again.
I've set the default for both these fields to NULL in both PHPRunner and the structure of the table and they still appear blank, but that doesn't seem to keep the fields NULL even if these fields are never touched. I was wondering if I could use an 'After record added' event to change the blank fields to NULL.
Or maybe there's a better way to get the number of people enrolled beside simply counting the name fields....
