Count items in column from another SharePoint list

Question

How can I count the instances that match the item in one list from another?
Please see scenario for example.

I have 2 SharePoint lists.

List A is a list where managers record scores for their associate audits.

Associate      Score
----------     ------
Associate 1     100
Associate 2     100
Associate 1     90
Associate 1     50
Associate 2     0
Associate 3     80

---

And List A grows on and on as new reviews are added.

List B is just a staffing list and only holds one instance of the associates name.
I want List B to count the number of times it appears in List A, so when a new item is added to list A, it automatically counts it in list B. For example:

List B

Associate      Count
----------         -----
Associate1      3
Associate2      2
Associate3      1

Something similar to a count for a query in Access or countif in excel, just for further clarification. Any suggestions?

Also, the Associate Field is a Group/People Type so it matches their userId, if that matters any. I would prefer to keep it that way, but I am open to suggestions. I am also using SharePoint 2007.

Answer 1

If your list A is using a lookup column to list B for the associate names, this should be pretty simple.

Simple go to list B, and add a new column that is a lookup to list A. When you drop down the fields to choose from, you should see Associate(count).

This will count the number of times each associate name is used in List A, and will display the count exactly how you want.

Cheers!