Count duplicates in a table and display in a query

[SltPhx]

Hello All,
Kindly help me to count the duplicates (based on a field) in a table and display the count along with certain calculated fields using a query.
I have a table with UserID, Number(Unique) and Internal/External.
For eg:

A1 2 Internal
B1 3 Internal
A1 4 Internal
C1 5 External
A1 6 External
C1 7 External

I want the query to display:

UserID Count Internal(s) External(s)

A1 3 2 1
B1 1 1 0


C1 2 0 2

Is this possible? Please let me know..
Thanks much for all your time

Warmest Regards,
SLTPHX

[TheShabz]

You're going to need 3 queries to calculate the different counts then a 4th to bring them together.
First:

SELECT USERID, Count(UserID) AS CountOfUSERID
FROM tableName

Second:

SELECT USERID, Count(UserID) AS CountOfInternal
FROM tableName
WHERE [Internal/External] = "Internal"

Third:

SELECT USERID, Count(UserID) AS CountOfExternal
 FROM tableName
WHERE [Internal/External] = "External"

Now, after saving each of those queries, you bring them into a 4th one, join on the USERID, then select each item. The result of this query should be what you want

[SltPhx]

Thank you soooooooo much

I tried what you suggested and it worked awesome

Although I had to add something to the queries..
I ran the query you explained and I got this error "You tried to execute a query that does not include the specified expressions as part of an aggregate function" - so I had to use a Group By clause to the queries.. and IT WORKED!


SLTPHX

[TheShabz]

Yea, I totally forgot about the Group by. Anytime you have Sum(), Count(), Max(), Min() etc, you have to do a group by on the "label" field. The Group By is one of the things that totally slip my mind when I'm typing out SQL. My apologies for missing it. Glad you got it working.

[SltPhx]

Thank you for explaining..