How can I count the number of MealIDs per User? I only know how to do it with two queries. 1

[keun]

My data is pasted at the end of this post. I am trying to count MealIDs per name. Right now I use two queries. How can I do this in one query?

First Query

SELECT tblSomeTable.name, tblSomeTable.MealID
FROM tblSomeTable
GROUP BY tblSomeTable.name, tblSomeTable.MealID;

Second Query

SELECT qryCountMealIDsPerPerson.name, Count(qryCountMealIDsPerPerson.name) AS [Count MealIDs]
FROM qryCountMealIDsPerPerson
GROUP BY qryCountMealIDsPerPerson.name;

"ID" "name" "MealID""qty" "food" "Date and Time"
1 "abe" "A001" 1.00 "orange" 1/2/2013 5:51:00
2 "abe" "A001" 3.00 "pear" 1/2/2013 5:52:00
3 "abe" "A001" 3.00 "orange" 1/2/2013 5:53:00
4 "abe" "A001" 2.00 "orange" 1/2/2013 5:54:00
5 "abe" "A002" 2.00 "plum" 1/2/2013 5:45:00
6 "abe" "A002" 1.00 "orange" 1/2/2013 5:46:00
7 "abe" "A002" 7.00 "onion" 1/2/2013 5:49:00
8 "abe" "A003" 2.00 "orange" 1/2/2013 6:00:00
9 "abe" "A003" 3.00 "orange" 1/2/2013 6:04:00
10 "abe" "A003" 2.00 "pear" 1/2/2013 6:12:00
11 "abe" "A003" 1.00 "orange" 1/2/2013 6:13:00
12 "jen" "A004" 2.00 "orange" 1/3/2013 7:15:00
13 "jen" "A004" 3.00 "orange" 1/3/2013 7:16:00
14 "jen" "A004" 2.00 "orange" 1/3/2013 7:22:00
15 "jen" "A004" 1.00 "orange" 1/3/2013 7:24:00
16 "jen" "A005" 2.00 "orange" 1/3/2013 7:22:00
17 "jen" "A005" 1.00 "orange" 1/3/2013 7:24:00
18 "jen" "A005" 3.00 "pear" 1/3/2013 7:27:00
19 "jen" "A006" 3.00 "apple" 1/3/2013 8:31:00
20 "jen" "A006" 3.00 "pear" 1/3/2013 8:37:00
21 "jen" "A006" 1.00 "orange" 1/3/2013 8:50:00
22 "jen" "A006" 2.00 "orange" 1/3/2013 8:53:00
23 "lisa" "A007" 2.00 "orange" 1/3/2013 7:18:00
24 "lisa" "A007" 1.00 "onion" 1/3/2013 7:21:00
25 "lisa" "A007" 3.00 "orange" 1/3/2013 7:27:00
26 "lisa" "A007" 1.00 "apple" 1/3/2013 7:41:00
27 "lisa" "A008" 2.00 "orange" 1/2/2013 5:43:00
28 "lisa" "A008" 1.00 "orange" 1/2/2013 5:45:00
29 "lisa" "A008" 2.00 "orange" 1/2/2013 5:50:00
30 "lisa" "A008" 2.00 "banana" 1/2/2013 5:58:00
31 "lisa" "A009" 2.00 "banana" 1/2/2013 7:15:00
32 "lisa" "A009" 2.00 "orange" 1/2/2013 7:15:00
33 "lisa" "A009" 1.00 "orange" 1/2/2013 7:17:00
34 "lisa" "A009" 2.00 "orange" 1/2/2013 7:19:00
35 "mike" "A010" 1.00 "orange" 1/2/2013 7:25:00
36 "mike" "A010" 3.00 "orange" 1/2/2013 7:27:00
37 "mike" "A010" 2.00 "orange" 1/2/2013 7:28:00
38 "mike" "A011" 2.00 "pear" 1/2/2013 8:22:00
39 "mike" "A011" 3.00 "pear" 1/2/2013 8:24:00
40 "mike" "A011" 3.00 "pear" 1/2/2013 8:27:00


I joined this forum in 2005. I am still a hack.

 

[Andrzejek]

[tt]Select Distinct Count(MealID)
From MyTable
Where Name = 'abe'[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[keun]

My goal is to end up with:

abe,3
jen,3
lisa,3
mike,2

I joined this forum in 2005. I am still a hack.

 

[PWise]

not tested

Select [name],count(*)
from tablename
group by mealid

 

[PHV]

SELECT A.name, Count(*) AS [Count MealIDs]
FROM (SELECT DISTINCT tblSomeTable.name, tblSomeTable.MealID FROM tblSomeTable) A
GROUP BY A.name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keun]

Thanks PH. Figuring this out and adapting it is going to save me a lot of time!

I joined this forum in 2005. I am still a hack.

 

[dhookom]

Keun,
Not that he needs more stars but you should recognize the question has been answered (as well as PH) by clicking the "Like this post? Star it!?

Duane
Hook'D on Access
MS Access MVP

 

[keun]

Thought I did... thanks for the remind.

I joined this forum in 2005. I am still a hack.