-
What could i do to get something like:
Code:
memberName count
-------------------------
Ace 12
Normal 34
Member 02
Those are my table(they more field, but for the purpose we wont need them)
Code:
Table:
-tblMemberType
-memberTypeId
-memberName
-tblPlayer
-playerMemberTypeId
I want to get the memberName and how many player have this memberName.
I can't get the Count function to work.
It's always the same error :....as part of an aggregate function.
-
Sebs,
I've just picked this up. I'll answer in the morning (Tuesday) when I've got a bit more time.
P.
-
Using DAO Object
this may not the shortest code but yet it does what you need...
Code:
Option Explicit
Private db As DAO.Database
Private rs As DAO.Recordset
Private rs2 As DAO.Recordset
Private sql As String
Private Sub Form_Load()
Dim result As String
Set db = DAO.OpenDatabase("C:\player.mdb", False, False)
sql = "SELECT DISTINCT memberName, memberTypeId " & _
"FROM tblMemberType " & _
"ORDER BY memberName ASC;"
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then
Debug.Print "memberName" & vbTab & "Count"
Debug.Print "-----------------"
While Not .EOF
sql = "SELECT Count(playerMemberTypeId) as nMember " & _
"FROM tblPlayer " & _
"WHERE playerMemberTypeId = '" & rs.Fields("memberTypeId") & "';"
Set rs2 = db.OpenRecordset(sql, dbOpenSnapshot)
If rs2.RecordCount <> 0 Then
result = String(128, Chr(32))
Mid(result, 1) = rs.Fields("memberName")
Mid(result, 13) = Format(rs2.Fields("nMember"), "00")
Debug.Print result
End If
rs2.Close
Set rs2 = Nothing
DoEvents
.MoveNext
Wend
End If
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
-
ADO Object
This another sample with ADO object
Code:
Option Explicit
Private conn As ADODB.Connection
Private rs As ADODB.Recordset
Private rs2 As ADODB.Recordset
Private sql As String
Private Sub Form_Load()
Dim result As String
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\player.mdb"
sql = "SELECT DISTINCT memberName, memberTypeId " & _
"FROM tblMemberType " & _
"ORDER BY memberName ASC;"
Set rs = New ADODB.Recordset
With rs
.Open sql, conn, adOpenKeyset, adLockPessimistic
If .RecordCount <> 0 Then
Debug.Print "memberName" & vbTab & "Count"
Debug.Print "-----------------"
While Not .EOF
sql = "SELECT Count(playerMemberTypeId) as nMember " & _
"FROM tblPlayer " & _
"WHERE playerMemberTypeId = '" & rs.Fields("memberTypeId") & "';"
Set rs2 = New ADODB.Recordset
rs2.Open sql, conn, adOpenKeyset, adLockPessimistic
If rs2.RecordCount <> 0 Then
result = String(128, Chr(32))
Mid(result, 1) = rs.Fields("memberName")
Mid(result, 13) = Format(rs2.Fields("nMember"), "00")
Debug.Print result
End If
rs2.Close
Set rs2 = Nothing
DoEvents
.MoveNext
Wend
End If
End With
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
-
Single SQL statement
Mean while I still can not archieve this with a single SQL statement. If anyone got the single SQL statement solution do post it & I would like to know how can it be done.
-
Single SQL
I'm using the SQL below to determine howmany times someone has looged onto an application. It's counts the succesfull logons and also give the last used logon date, all in one SQL.
strSQL = "SELECT tblLog.Username, Last(tblLog.LogDate) AS LastOfLogDate, Count(tblLog.Succes) AS CountOfSucces FROM tblLog GROUP BY tblLog.Username ORDER BY tblLog.Username, Last(tblLog.LogDate)"
I have no time to convert this to your requirements, but maybe it gives you an idea. I just used a Group By in a query and used Count on the Succes field.
-
Hi! Berthil, I think you case is a bit different because all your data seem to be from the same source table.
-
This can be accomplished using:
Code:
SELECT tblMemberType.memberName, Count(tblMemberType.memberName) AS memberCount
FROM tblPlayer INNER JOIN tblMemberType ON tblPlayer.playerMemberTypeId = tblMemberType.memberTypeId
GROUP BY tblMemberType.memberName;
Should work.
Cheers,
P.
-
Good Job
Paul has done a good job. I suspected yall had some knowledge of SQL to put that together.
You can think of MSACCESS what you want to think but I use it to build complex queries and copy the SQL to a string in VB. Works fine every time. ;)