[thelist] Data Shaping and Count

[Michele Foster (WizarDev)]

I have this query ....

strShapeSQL = "SHAPE {SELECT JobID, PositionEn, ApplicationDeadline,
Uniterra" &_
" FROM Jobs WHERE Uniterra=Yes ORDER BY ApplicationDeadline DESC}" & _
" APPEND({SELECT VolApply.VolApplyID, VolApply.VolApplyContactID,
VolApply.DateSubmitted, VolApply.Position, Contacts.ContactID,
Contacts.FirstName, Contacts.LastName" &_
" FROM VolApply INNER JOIN Contacts ON VolApply.VolApplyContactID =
Contacts.ContactID ORDER BY VolApply.DateSubmitted} AS Applicants " & _
" RELATE JobID TO Position)"

Which gives me,

JobTitle1 (PositionEn)
    Applicant1 (FirstName, LastName)
    Applicant2
    Applicant3

JobTitle2
    Applicant1
    Applicant2
etc.

This all works fine.  Now, what the client would like, is to know the total
number of jobs a particular applicant has applied for.  I'm clueless how I
could add that to the above SQL.

In a perfect world .. they'd like to go one step further and do this ..

JobTitle1
    Applicant1
        This person has also applied for the following jobs:
            List of other jobs applied for

However, I think that's asking too much, and what they've said would meet
their needs, is just to have the total number of positions applied for on
the current screen, that number is clickable, then they could see all of the
positions on the next screen.

Any help greatly appreciated.

Michele