how to get the last non empty column in ms access

so i have this huge database for my school project it goes like this

id	team	game1	score1	game2	score2	game3	score3
1	barca	vs real	2-1	vs bvb	5-2	vs atletic	0-3
2	real madrid	vs barca	1-2	vs betis	3-0
3	man city	vs man united	1-2

and i want to make a query that will give me only the last game of each team in excel its easy but i cant do it in ms access result that i need is

id	team	last game
1	barca	vs atletic
2	real madrid	vs betis
3	man city	vs man united

Solution

One thing that has been commented on is that your database design needs to be fixed - you don't go across (as you would in Excel), but down as extra rows in tables. There is a limit of 255 fields in an Access table that you need to be aware of.

However, if you decide to stick with this, a different way of approaching it would be to create a small VBA function that loops the fields backwards to get the answer that you need. Something like:

Function fLastMatch(lngTeamID As Long) As String
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim lngLoop1 As Long
    Set db = DBEngine(0)(0)
    strSQL = "SELECT * FROM tblFootball WHERE id=" & lngTeamID
    Set rs = db.OpenRecordset(strSQL)
    If Not (rs.BOF And rs.EOF) Then
        For lngLoop1 = rs.Fields.Count - 2 To 2 Step -2
            If Not IsNull(rs.Fields(lngLoop1)) Then
                fLastMatch = rs.Fields(lngLoop1)
                Exit For
            End If
        Next lngLoop1
    End If
fExit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fLastMatch", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

This works because the last (nth) field in the recordset is position n-1 as recordsets are 0-indexed (the first field is in position 0, the second field is position 1.....). This means that the last match field is in position n-2. So we start at this position in the fields and check if there is data. If there is, we have found the last match played and can exit the loop. If there is no data, then we go back two fields, to the previous match, and then repeat the checks.

You can then use this function in a query to get the answers that you require:

SELECT id, fLastMatch(id) AS LastMatch
FROM tblFootball;

This approach means that you don't need to worry about how many matches are included and add the correct number of Nzs in if this changes over time (which is a bad idea in a database).

Regards,