Whenever a team is assigned to an operation or action (collect, identify, verify, etc.), or when a team-level administrative task (delete, new, edit) is executed, the user can browse through the existing teams. Teams are represented as a shortlist of their members, in the order of seniority, as shown in figure 3.
Fig. 3: Person team view and selection
Form layout after Berendsohn & Raisin (unpubl.)
Teams have to be composed using the three tables depicted in fig. 2. The team has to be aggregated from its members, who are represented by one record each in the table Person Team Member. This can be achieved by the following query with its underlying aggregate function, which is detailed in statements [2] - [10]:
[1] SELECT member(Team_ID)AS Members FROM Person Team Member GROUP BY Team_ID ORDER BY member(Team_ID)
The clause in the SQL-Query "GROUP BY Team_ID" maps all records with the same team_id to one record in the result set. Since many records are mapped to one record, it is necessary to use an aggregate function to obtain each attribute in the result set (an aggregate function maps from a multidimensional definition domain to a unidimensional result domain).
The user-defined aggregate function member() is executed once for each team and uses team_id as parameter. It returns a string with values of defined attributes of the team members in sequence of seniority (e.g. the standard abbreviation for taxonomic authors, or, as here depicted, last name and initials). This function is composed as follows:
The source data structure for the function is a recordset, which consists of the team_id and the person's data, which is achieved by joining the tables Person Team Member and Person in an SQL-statement. For the sake of performance, the result is sorted by team_id and Sequence:
[2] rstTeam = db.OPenRecordset("SELECT Team_ID As Nr, LastName, Initials
FROM Person Team Member As PTM, Person
WHERE PTM.Person_ID = Person.Person_ID
ORDER BY Team_ID, Sequence", DB_OPEN_DYNASET)
The passed parameter team_id is searched in this recordset:
[3] If rstTeam!Nr <> Team_ID Then rstTeam.FindFirst Nr = Team_ID
The following statements then generate a string consisting of the team members, in sequence of their seniority:
[4] Do While rstTeam!Nr = Team_ID Str = Str & ", " & rstTeam!LastName & ", " & rstTeam!Initials rstTeam.MoveNext Loop member = Str
In programming larger database applications in MS Access, performance aspects play a mayor role. If the function defines a recordset every time it is called, performance degrades by a factor of about 10 - the function is called once for each team in statement [1]. Therefore the database (db) and recordset (rstTeam) variables are declared as static [5]. The recordset is defined only once, in an error handling routine [8] which is evoked when the static variable rstTeam is declared and the recordset is not defined [6]. Another error trapped here is the end-of-file condition of the recordset, which is handled by statement [9].
[5] Function member (Team_ID As Long) As String
Static db As Database, rstTeam As Recordset
Dim Str As String
[6] On Error GoTo E_Member
[3] If rstTeam!Nr <> Team_ID Then rstTeam.FindFirst "Nr = " & Team_ID
[7] Str = rstTeam!PersonName
rstTeam.MoveNext
[4] Do While rstTeam!Nr = Team_ID
Str = Str & ", " & rstTeam!LastName & ", " & rstTeam!Initials
rstTeam.MoveNext
Loop
member = Str
Exit Function
[8] E_Member:
Select Case Err
Case 91 ' Object variable is not set --> on first call
Set db = DBEngine.Workspaces(0).Databases(0)
[2] rstTeam = db.OPenRecordset("SELECT Team_ID As Nr, LastName, Initials
FROM Person Team Member As PTM, Person
WHERE PTM.Person_ID = Person.Person_ID
ORDER BY Team_ID, Sequence", DB_OPEN_DYNASET)
Resume
[9] Case 3021 'No actual record --> recordset at the end of file
rstTeam.MoveFirst
Resume
[10] Case Else
' Place for other error handling
Resume Next
End Select
End Function
This function has been tested and found to perform adequately with the Authors of Plant Names tables (Brummitt & Powell 1992) which consist of more than 30000 person entries.