There area various approaches:
1. Use a crosstab query. This can cope with an unknown number of categories.
2. As in this case you have a known number of categories you can
conditionally aggregate the data by summing expressions which return 1 or 0
depending on the category. In a query you'd do this:
SELECT
SUM(IIF([Membership Type] = 1,1,0) AS MemberCount,
SUM(IIF([Membership Type] = 2,1,0) AS FellowCount,
SUM(IIF([Membership Type] = 3,1,0) AS RegisteredCount,
SUM(IIF([Membership Type] = 4,1,0) AS AcssociateCount
FROM Clients;
You can base a report, a form or an unlinked subreport or subform in a
parent form/report on this query.
3. You can use the DCount function to count the numbers of rows in the
Clients table which meet each criterion for type of membership, e.g. for
Members:
= DCount("*", "Clients", "[Membership Type] = 1")
You could use this expression, and similar ones for the other membership
types as the ControlSource of a text box in a form or report.
Incidentally. you might sometimes see a method recommended for conditional
aggregation which exploits the fact that in Access TRUE is -1 and FALSE is 0,
e.g. in a query:
SUM(([Membership Type = 1)*-1) As MemberCount
or
SUM(ABS([Membership Type = 1)) As MemberCount
DON’T DO THIS! Relying on the implementation in this way is bad programming
practice.