Group By and Count different than Select query records

[debbieg]

I have the following tables:

Teams
TeamNum
TeamYear
...

Players
MemNum
TeamNum

I have trying to get what I want all afternoon! I want to get the number of players by year.

Players can play on more than one team per year and I want to count them only once. I've done a select query with Unique Values = yes using only TeamYear and MemNum and getting the correct count but when I add the Totals row and group by TeamYear and count MemNum my counts are way off.

What am I doing wrong?

Thanks for any help.

Debbie

 

[dhookom]

It would help if you provided some sample records and attempts at your SQL.

I think your issue might be that you have nothing that stores which year a MemNum played for a TeamNum.

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

maybe:

SELECT TeamYear, Count(*) FROM
(select MemNum, Max(TeamNum) From Players Group By MemNum) P
INNER JOIN Teams T ON P.TeamNum = T.TeamNum
GROUP BY TeamYear

Leslie

Have you met Hardy Heron?

 

[BigRed1212]

with data like

table teams2
teamnum	year
1	2006
2	2006
3	2007
4	2007
5	2007
6	2008
7	2008
8	2008
9	2008

and

table players2
memnum	teamnum
12	1
13	4
14	5
15	6
16	6
12	5
13	9
14	6
15	9
19	1
20	2
12	4
15	3
21	7
21	8

something like

select memnum, count (memnum) as 2008_teams_played_on from

(
SELECT memnum, year,players2.teamnum
from players2 left join teams2 on players2.teamnum = teams2.teamnum 
where year = "2008"
)

group by memnum

gives you the 5 members who played on teams in 2008 and the number of teams each one of them played on.

 

[debbieg]

Thanks for your responses.

The 2 tables are joined on TeamNum. I can see what players are on each team and what teams each player has played on. The data that BigRed1212 showed is a good example.

I very unfamiliar with subqueries. I cannot get either of the example code to work without errors. I'll have to do some research on them.

If I split them out, I can get LesPaul's example to work separately but not when I put them together.

Again, I am wanting to get the total number of players per year, counting a player once since they can play on more than one team per year.

Thanks,
Debbie

 

[dhookom]

How does your data tell you which year a player played on a team? Are the TeamNums different each year?

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

what version of access are you using?

Leslie

Have you met Hardy Heron?

 

[dhookom]

Debbie,
Can you provide a few sample records from each table to illustrate your problem? Also, provide how you would expect these records to display in your final output.

Duane
Hook'D on Access
MS Access MVP

 

[debbieg]

Some more information:

Teams
TeamNum (Primary key) (auto number)
TeamYear
...

Players
MemNum (Foreign key) (Primary key)
TeamNum (Foreign key) (Primary key)

How does your data tell you which year a player played on a team?

When the above 2 tables are joined on TeamNum that's how I know which team(s) they played on which gives me which year they played.

what version of access are you using?

2002 SP3

Can you provide a few sample records from each table to illustrate your problem?

Players
MemNum TeamNum
1 7
1 17
1 45
1 56
2 1
2 61
22 7
22 17
22 45
22 56
54 1
54 61

Teams
TeamNum TeamYear
1 2006
7 2006
17 2006
45 2007
56 2007
61 2007

how you would expect these records to display in your final output.

2006 275
2007 321
2008 225

Thanks for helping,
Debbie

 

[debbieg]

I can get what I want with 3 queries:

qryPlayersByYear gives me what team every member played on in each year.

SELECT Players.MemNum, Teams.TeamYear, Teams.TeamNum
FROM Teams INNER JOIN Players ON Teams.TeamNum = Players.TeamNum;

qryPlayersByYear_2 gives me the year every member played in.

SELECT DISTINCT qryPlayersByYear.TeamYear, qryPlayersByYear.MemNum
FROM qryPlayersByYear;

qryPlayersByYear_3 gives me the correct count of the number of members that played in each year.

SELECT qryPlayersByYear_2.TeamYear, Count(qryPlayersByYear_2.MemNum) AS CountOfMemNum
FROM qryPlayersByYear_2
GROUP BY qryPlayersByYear_2.TeamYear;

Is there any way to join these into 1 query?

Thanks,
Debbie

 

[PHV]

SELECT TeamYear, Count(*) As NumberOfMembers
FROM (SELECT DISTINCT T.TeamYear, P.MemNum FROM Teams T INNER JOIN Players P ON T.TeamNum = P.TeamNum) D
GROUP BY TeamYear

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