Conditional Count

[sandsingh]

Table Name: BufTable
Structure/Data:

UserID Category
U1 Category A
U1 Category A
U1 Category C
U2 Category A
U2 Category C
U2 Category D
U3 Category A
U3 Category C
U3 Category D

Desired Result:
UserID Count of Category A Count of Category B Count of Category C Count of Category D
U1 2 0 1 0 etc.

Help!

 

[DCrake]

Take a look at cross tab queries

 

[sandsingh]

When I try using the cross tab queries, Access throws up a message that it needs more than 2 fields to work with.

 

[DCrake]

Create a normal select query and have the following fields

UserRef:UserID
CatType:Category
Cnt:1

Save this query and us it for your crosstab when it asked you for values select cnt to be counted

 

[sandsingh]

Thanks David, works wonders

 

[Brianwarnock]

Another approach is to use conditional sums

In the design grid USERID CountofCategoryA:IIF(Category="A",1,0) etc
then in the Totals row Group BY UserID and SUM the Countof columns

Brian

 

[sandsingh]

Thanks, Brain. Let me try that one too