I have a table:
id date status
====================
1 03/01/2006 W
1 02/01/2006 L
1 01/01/2006 L
1 31/12/2005 W
...
2 03/01/2006 L
2 02/01/2006 L
2 01/01/2006 W
2 31/12/2005 W
...
3 03/01/2006 W
3 02/01/2006 W
3 01/01/2006 W
3 31/12/2005 W
...

What I want is to concat the field status showing the latest 5 results, like this:

id status
=========
1 WLLWL
2 LLWWL
3 WWWWW

I have tried to use concat, but i can't get the desired one. Any idea to do that?

Thanks!

BNT,

You're gonna have to use VBA to do this. In your query put:

StatusField: fnGetLast5([id])

Then, in a Public module put:


Public Function fnGetLast5(lngID As Long) As String
Dim rst As DAO.RecordSet
Dim strTemp As Temp

strTemp = ""

Set rst = CurrentDB.OpenRecordSet("Select Top 5 Status " & _
"From YourTable " & _
"Where id = " & lngID & " " & _
"Order By Date DESC"
While Not rst.EOF And Not rst.BOF
strTemp = strTemp & rst!Status
rst.MoveNext
Wend

fnGetLast5 = strTemp
rst.Close
Set rst = Nothing
End Function


btw, Date is reserved for use by Access.

hth,
Wayne

I am a newbie using Access... I have added this code into a new module Module1. What should i do next so that i can use this function?

Thanks a lot!!!

Done. Thanks so much!