The following requirements were defined for the composition of a new person team:
Fig. 4: Composing a team
Form layout adapted from Berendsohn & Raisin (unpubl.)
Fig. 4 illustrates the form used to compose teams. Members may be added or deleted from the selection (listbox to the right), and new persons may be added to the list of persons (left). The seniority (sequence from top to bottom) may defined by the sequence of input, however, it may also be changed subsequently.
To enforce the second and third rule the members of a new team have to be examined before they are written to the Person Team and Person Team Member tables. Since Access-SQL supports neither transactions nor temporary tables, a table New Team is created with person_id and Seniority as its attributes. By defining a unique index on person_id in the New Team table the double entry of a person can be prevented (requirement 2).
To check, whether the new team (in the New Team table) is a duplicate it is necessary to examine the existing teams step by step for each and every member. At this point it becomes apparent that an algorithm which combines the strength of Access Basic and SQL is more powerful than the solution using only either Access Basic or SQL. Pressing the OK button triggers the following procedure, which checks the input against the data in the system.
A recordset, rstNewTeam, is defined in the procedure which consists of the name and seniority of the members of the new team. The total number of members in the team is assigned to a variable, CountMem:
[11] rstNewTeam = db.OpenRecordset ("New Team" OPEN_DB_DYNASET)
rstNewTeam.MoveLast
CountMem = rstNewTeam.RecordCount
The following SQL query has to be generated in the procedure, because its construction depends directly on attribute values of the recordset defined in [11]. Although an SQL query is always executed in its entirety, to facilitate understanding it is here explained in the sequence of its construction. It first scans all existing teams for those which start with the same members in the same sequence as the new team [12]. Subsequently, all teams having more members than the new team are discarded [13]. The query generated in statements [12] and [13] is written into a string variable (str) which is used in statement [14] to define the recordset containing (at most) the team which is identical to the new one. Statement [15] then evaluates the contents of this recordset.
The SQL statement [12] finds all teams which have the the same first member. In a subquery using the "In"-clause in a loop, the result set is further abridged by querying sequentially for each additional member of the new team.
[12] rstNewTeam.MoveFirst Str = "SELECT Team_ID FROM Person Team Member WHERE "Person_ID = " & rstNewTeam.person_id & " AND Seniority= " & rstNewTeam. Seniority & "" rstNewTeam.MoveNext Do Until rstNewTeam.EOF Str = Str & " AND Team_ID IN SELECT Team_ID FROM Person Team Member WHERE Person_ID = " & rstNewTeam!person_id & " AND Seniority= " & rstNewTeam. Seniority& "" rstNewTeam.MoveNext Loop
The produced result set contains all teams which start with the same members in the same sequence as in the new team. Now those teams which have additional members have to be excluded. The WHERE clause in the SELECT statement in [13] limits the result set to all records from the Person Team Member table, which have the same team_id as the teams in the result set. The result is then grouped by team_id using the Access Basic aggregate function count(), which counts the occurrence of each team_id. Finally, the HAVING clause selects only those teams, which have the same number of members as the new team:
[13] Str = "SELECT Team_ID FROM Person Team Table
WHERE Team_ID IN ("& Str & ")
GROUP BY Team_ID
HAVING Count(Team_ID) = " & CountMem & ""
A recordset is defined on the basis of the query,
[14] rstPotTeams = db.OpenRecordset (Str, DB_OPEN_DYNASET)
and the routine branches according to the presence or absence of teams in the recordset:
[15] If rstPotTeams.EOF and rstPotTeams.BOF Then ' Add New Team Else Exit Sub Endif
If the recordset contains record(s), then the new team is a duplicate and must not be added to the database. References to the new team will be directed to the existing one. Otherwise the new team can be added to the tables.