Counting members & selecting names [Resolved]

[olamm2k]

Hi,

I'm using the same tables as in my previous post ( http://www.vbforums.com/showthread.php?t=328403 ), but I now need to count the number of members present on a date.

The query I have so far is:
SELECT Members.MemID, COUNT(RegMem.MemID) AS NumPresent FROM Members LEFT JOIN RegMem ON Members.MemID = RegMem.MemID GROUP BY Members.MemID;

This returns a list of MemIDs against the number of sessions that they have been to, but I also need the names of the members, which are contained in the Members table and are matched uniquely to their MemID. I could do this in a second query, moving through each MemID in the first query to obtain a name, but I think it's possible in one query. I don't need the MemID returned and I can't just GROUP BY Surname because some members are siblings.

Any ideas?

[olamm2k]

Just experimented some more - looks like I can just do:
SELECT Members.Surname & Chr(44) & Chr(32) & Members.Forenames AS FullName, Count(RegMem.MemID) AS CountOfMemID
FROM Members LEFT JOIN RegMem ON Members.MemID = RegMem.MemID
GROUP BY Members.Surname, Members.Forenames;

[PilgrimPete]

Hi again,

Do you not need SDate in your SELECT and GROUP BY clauses too? (To get the number present on each date.)
Also, I think you'll be better with an INNER JOIN this time

[olamm2k]

Well, I'll be modifying the query, but it's a bit different this time. I'm producing a report with member details, and one of the fields that I want to put on the report is how many sessions the member has been to in the past year, so it's not dependent on SDate, but on MemID.

The reason it seems a little odd is because I was thinking of two separate reports - one for the sessions, one for the members. I suppose I just need to use:
"SELECT COUNT(MemID) AS SessionsAttended FROM RegMem WHERE MemID = " & intMemID & ";"

The first query was just to display a list of all the members, and how many sessions they've been to.

[olamm2k]

Thinking about it, I need to extract all of the details from the Members table (Surname, Forenames etc) where the field MemID is equal to some passed intMemID along with the number of times that that MemID appears in the table RegMem.

What's the best way to do this? Obviously, I could do it in two separate queries, but what about in one?

[PilgrimPete]

I'd have thought your original query would be pretty close. Something like this:

strSQL = "SELECT mem.Surname & Chr(44) & Chr(32) & mem.Forenames AS FullName, Count(reg.MemID) AS SessionAttended " & _
     "FROM Members mem " & _
     "INNER JOIN RegMem reg " & _
     " ON mem.MemID = reg.MemID " & _
     "WHERE mem.MemID = " & intMemID & " " & _
     "GROUP BY mem.Surname, mem.Forenames"

[olamm2k]

That's great, thanks!

As to the other report, I can just use:
SELECT COUNT(SDate) AS NumAttended FROM RegMem WHERE SDate = dteSDate;