Totals of junction tables for use in filters
Here's a sketch of my structure:
People (ID - pk , OrgID - fk to Organisations::ID, contact details)
Events (ID - pk)
Delegates (ID - pk, personID - fk to People::ID, eventID - fk to Events::ID)
So, technically speaking delegates is a junction of people and events.
All is pretty straightforward and working quite well for our booking purposes, but now of course we have a marketing person who wants to segment the mailing list. I need to be able to get
1: the number of delegates an organisation has sent to an event
select orgID, EventID, count(delegateID)
group by organisation, event
We are using FM11 so can't use SQL, and they want the column to be exposed on the delegate entry form for filtering purposes prior to an export.
Help please, I can't make it work in Filemaker.