Posted on 2002-05-06
I hava a table in Microsoft Access.
The table name is real_progress.
Below is the structure + value of the table.
Now,i'd like to write a sql query to get the Sheet_No whereby this Sheet_No is the
LAST record for that month.
So,the result i expect is 110,145,170 and 200
Meanwhile,i manage to get this result using 2 loops.But i wonder can i done this in a faster way?
Dim query as String
Dim query1 as String
Dim m as Integer
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
query = "select distinct Month(Date) as month_temp from real_progress where Student_ID=100 order by Month(Date)"
Set rs = cn.Execute(query)
If Not rs.EOF Then
Do While Not rs.EOF
m = rs("month_temp")
query1 = "select Sheet_No from real_progress where Student_ID=100 and Month(Date) = " & m & " order by Date desc"
Set rs1 = cn.Execute(query1)
If Not rs1.EOF Then
Debug.Print m & "<>" & rs1("Sheet_No")
Set rs1 = Nothing
MsgBox "no record"
Set rs = Nothing
Thank You in advance.