Sum No of Members Wrong

[Kassy]

I have a query where I am trying to count the number of members who have joined during a period of 12 months from a start date sept 01 to following August 31. Instead of getting the total number of members I am getting totals for each member type. I have 12 member categories identified by [TypeID] but each member has a MemberID (Autonumber). I use Sum in the MemberID Field in my query by pressing the Sigma Button. When I run the query I get totals for each TypeID instead where I wanted one final Total. I suppose I need to count the records bearing in mind some numbers have been deleted so numbers jump in the Auto Number field. This is quite asic but I cant get it right. Help appreciated!

 

[RV]

I use Sum in the MemberID Field in my query

You should use Count (a full count that is).

RV

 

[neileg]

The aggregate functions like sum or count only operate on identical records as they appear in the query. If you are including the TypeID in the query, this will make the records non-identical and therefore you will get a figure for each TypeID.

 

[Kassy]

I tried Count

Sorry I checked - it was 'count' I used in the Query however this returns individual records as 'Neileg' says. So how do you use count in a query that has all unique records. Even if I hadn't include the TypeID field most members have different names and different payment dates etc. So basically allmost all records are unique with Autonumber anyway. Now what? It must be about records so how do you count records?

 

[neileg]

Two things
1. If its a count you're after, why do you need all those fields? Just include the MemberID and the date field you are testing on, but unclick the Show checkbox so the query only returns the MemberID and counts that.
2. Try using DCount() instead of a query.

 

[Kassy]

Reduced my fields as you said however still get a list of members but not total count only count of list of different member Types. I want to count all members irrespective of their type. Sorry to be so stupid but how do I use DCount instead of a query?

 

[neileg]

Your SQL should look something like this:

SELECT Count(tblMyTable.MemberID) AS CountOfMemberID
FROM tblMyTable
HAVING (((tblMyTable.JoinDate)>=#1/1/2006#));

You would use DCount in a form or report if all you wanted was a count of the members. Have a look in Access help first

 

[Kassy]

Many Thanks it works!