Problem with query, AGAIN!!!!!

[sebs]

What could i do to get something like:

memberName        count
-------------------------
Ace               12
Normal            34
Member            02

Those are my table(they more field, but for the purpose we wont need them)

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.

[paulw]

Sebs,

I've just picked this up. I'll answer in the morning (Tuesday) when I've got a bit more time.

P.

[Chris]

this may not the shortest code but yet it does what you need...

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

[Chris]

This another sample with ADO object

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

[Chris]

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.

[Berthil]

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.

[Chris]

Hi! Berthil, I think you case is a bit different because all your data seem to be from the same source table.

[paulw]

This can be accomplished using:

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.

[Berthil]

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.