How to calculate how many of a attribute you have

[weilder02]

Basically I have a table that has a userID, a contactID and a type (friend, neutral & block) - what I want to do is to count how many of each type each user has - is there an easy way to do this?

[Datagopherdan]

You can create a query using your user field and type. Then select "Totals" from the toolbar and in the "Total" column. Select "Group By" for the user and "Count" for the "Type".

[weilder02]

That does it sort of - that gives me the userID with the total of how many contacts they have - what i want it to do is to give a count of how many of each type they have eg:

Results I'm getting at the moment:

UserID CountOfType
1 6
2 6

What I'd Like it to give:

UserID Friend Neutral Block
1 4 1 1
2 3 3 0

[Datagopherdan]

You can do that a few different ways. Probably the best would be to use a crosstab query. I won't write all the specifics on it but if you do a google search on access crosstabs, you'll find all the information that you need. Another approach would be to create a field for each of the types in a query like this.

Friend:IIF(nz([Type])="Friend",1,0)

You would do this for each of the types and set those fields to "Count". This wouldn't be the best way to do it though because it's hard coded. You would need to create a new field each time a different type is added or modified. The crosstab would be your best bet.

[weilder02]

Thanks for that - the crosstab query worked beautifully

[Datagopherdan]

Glad you got it working.

Thanks for that - the crosstab query worked beautifully