Help with count...

[phatrabbitzz]

Say I have the following database in Access:

Letter Name
(blank) Joe
c Joe
c Joe
c Sue
d Joe
c Sue

How can I know the know letter count without duplications for each unique name?
So for Joe I want it to return 3, (= blank + c + d).
For Sue it should return 1, only c is unique.

TIA!

 

[Jon K]

Do can do it in two queries.

qryOne:-
SELECT DISTINCT [Name], [Letter]
FROM [TableName]

qryTwo:-
SELECT [Name], Count("*") AS [Count]
FROM qryOne
GROUP BY [Name]


Run the second query.
.

 

[phatrabbitzz]

Jon, thank you very much!

Say I add a number column so the table looks as follows:

Letter Name Number
(blank) Joe 2
c Joe 4
c Joe 1
c Sue .5
d Joe 3
c Sue 1


Here is what I want to know:
1. What are the unique names I have in Name? (ie. joe, sue)
2. How many of that unique name is there in Name? (ie. Joe appears 4 times in Name)
3. For each unique name what's the avg. of Number (ie. avg for joe is 2.5 (=10 / 4)?
4. For each unique name how many unique letters in Letter are there? (ie. joe has 3, blank, d, and c)

So for Joe the answers would be:
1. Joe
2. 4
3. 2.5
4. 3

The two query's you posted give me the answer to #4.

I know if I put the following in a seperate query I'll get answers to #1,2,3.

SELECT test.Name, Count(test.Name) AS [Count], Avg(test.Number) AS AvgOfNumber
FROM test
GROUP BY test.Name;

Question is how do I combine your answer to #4 and my answer to #1,2,3 so I can see it all in one table? (ie. Joe, 4, 2.5, 3)

Thank you!

 

[Jon K]

You can join your query to my qryTwo in one new query.

SELECT yourQuery.Name, yourQuery.Count, yourQuery.AvgOfNumber, qryTwo.Count as CountOfLetter
FROM yourQuery INNER JOIN qryTwo ON yourQuery.Name = qryTwo.Name;
.

 

[phatrabbitzz]

Thank you Jon!

 

[phatrabbitzz]

One more question...sorry. If I have the same table:

(blank) Joe 2
c Joe 4
c Joe 1
c Sue .5
d Joe 3
c Sue 1

After I run qryOne, (distinct) I get the following:
(blank) Joe
c Joe
c Sue
d Joe

How can I get it so I also get the summation of the numbers for the distincts?
So I would get the following:
(blank) Joe 2
c Joe 5 (=4+1)
c Sue 1.5 (=1+.5)
d Joe 3

Thanks again!

 

[Jon K]

Instead of using Distinct in qryOne, you can change it to a Totals Query and use Sum to return the total numbers in the query:

New qryOne:-
SELECT [Name], [Letter], Sum([Number]) AS SumOfNumber
FROM [Test]
GROUP BY [Name], [Letter]

.

 

[phatrabbitzz]

Jon, Thanks again for the fast reply!

 

[phatrabbitzz]

Now If I have all these other columns like the following:
(blank) Joe 2 Z S A 9
c Joe 4 T S A 8
c Joe 1 T S A 7
c Sue .5 Y S B 6
d Joe 3 W S A 5
c Sue 1 Y S B 4

How can I get it so eliminate duplicates keeping based on the first column? And for the last column I just want to take the first one of the duplicates and output it.
So I would get the following:
(blank) Joe 2 Z S A 9
c Joe 5 T S A 8
c Sue 1.5 Y S B 6
d Joe 3 W S A 5

Thank you!!