[thelist] Data Shaping and Count

Michele Foster (WizarDev) michele at wizardev.ca
Mon May 9 10:17:22 CDT 2005

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)


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 ..

        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.


More information about the thelist mailing list