Multi-concatenation in a query

[steveflanuk]

I have a database which I record soccer matches for a local amateur club.

At the end of each season I create a document showing the details of all matches played during the season.

The database is working well, but I am trying to enhance the report somewhat.

The problem is around substitutions.

Basically, the line-up part of the report, run from a query, currently shows as follows:

#1 Player A
#2 Player B
...
#11 Player C

The underneath this I have the substitutions from another query:

#12 Player D (replaced Player C after 70 mins)

The problem is that every now and then, a substitute may be substituted. rarely happens, but is possible.

In cases such as this, the substitutions bit of the report would show as follows:

#13 Player E (replaced Player D after 85 mins)

As I said I want to enhance the report to show as follows:

#1 Player A
#2 Player B
...
#11 Player C (#12 Player D after 70 mins)

This would not normally be a problem, however, I do get a problem when I try and create the enhanced report to show substituted substitutes.

In this case I would want the record to show as follows:

#11 Player C (#12 Player D after 70 mins, #13 Player E after 85 mins)

Now I know I could just create a second query and concatenate the results, however, sometimes (very rarely in regular matches, but can happen in friendly matches) - a player may be substituted by another player who is in turn substituted by a third player who in turn is substituted by a fourth player!!

So, I would like the line in my report to show as follows:

#11 Player C (#12 Player D after 60 mins, #13 Player E after 75 mins, #14 Player F after 85 mins)

I think I can do this if the substitutions are made in shirt number order, but then run into problems if they aren't. For example:

#11 Player C (#13 Player E after 60 mins, #12 Player D after 75 mins, #14 Player F after 85 mins)

The underlying tables are:

tblMatches - holds basic data for each match (the opponents, venue, referee, etc) with each match having a unique autonumber.
tblPlayers - holds basic data for each player (name, dob, etc) with each player having a unique autonumber.
tblLineUps - gives the line-ups for each match and is populated by a form which links to tblMatches and tblPlayers, showing as follows:

MatchID (gets the autonumber from tblMatches)
PlayerID (gets the autonumber from tblPlayers)
Shirt (numerical field to put the players in a specific position in the line-up from 1 to 11 for starters and 12 onwards for substitutes equating to the shirt number [we don't use squad numbers yet!!])
ReplacedID (autonumber from tblPlayers showing who the substitute replaced on the field)
ReplaceMins (the time the player was on as a sub)

I did manage to create a result in Excel, using Countifs and Sumifs, however, when I try and replicate this in Access (obviously not using Countifs and Sumifs - but Access based functions), I'm getting a circular reference error and no results.

Anyone got any ideas?

Many thanks in advance.

 

[stopher]

You could use a recursive function like this:

Public Function GetSubs(PlayerID As Integer, MatchID As Integer) As Variant

Dim SubID As Variant
Dim subTime As String

SubID = DLookup("[PlayerID]", "tblLineUps", "[MatchID]=" & MatchID & " AND [ReplacedID]=" & PlayerID)

If IsNull(SubID) Then
    GetSubs = Null

Else
    subTime = CStr(DLookup("[ReplaceMins]", "tblLineUps", "[MatchID]=" & MatchID & " AND [PlayerID]=" & SubID))
    subPlayer = DLookup("[PlayerName]", "tblPlayers", "[PlayerID]=" & SubID)
    GetSubs = "#" & CStr(SubID) & " " & subPlayer & " after " & subTime & "mins" & (", " + GetSubs(CInt(SubID), MatchID))
End If

End Function

See the attached example (run Query1). The function will keep calling itself until no more substitutions are found.

hth
Chris