0

I'm building a tracking system where up to three members of the team can be responsible for each item. I'm trying to build a query to count the number of times that a team member's name ([FullName]) appears in any of the responsible fields ([Responsible],[Responsible2],[Responsible3]).

Example Responsible Table

So for this example I would like a query that would look in my team table and my responsible table and output something like this.

Desired output

I've tried

SELECT tblTeam.FullName, Count([FullName]=[Responsible] Or [Fullname]=[Responsible2] Or [FullName]=[Responsible3]) AS CountResp
FROM tblTeam, qryItems
GROUP BY tblTeam.FullName;

but I'm getting results where people who should have zero values don't, some people who should have positive value have numbers too high, and one person with many responsible instances has a number too low. Can anyone point me to my rookie mistake here?

Myles
  • 176
  • 8
  • Can you share the wrong result you're getting, and explain how it's wrong? – Mureinik Apr 23 '15 at 16:22
  • You're doing a cartesian join on `tblTeam`, `qryItems` if that's not intentional it would explain the inflated counts – FuzzyTree Apr 23 '15 at 16:25
  • @FuzzyTree Sorry I'm brand new to SQL. What is the alternative to a cartesian join and how would I decide when to use it? – Myles Apr 23 '15 at 16:36
  • 1
    You probably want an inner join. See http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins – FuzzyTree Apr 23 '15 at 16:40
  • @FuzzyTree Great post but I don't have a JOIN in my code whereas all of the examples in the post do. Is JOIN implied in my FROM arguement? – Myles Apr 23 '15 at 18:59

2 Answers2

2

a simple approach is to union the three columns and then do a group by with a count(*). Something like the following:

select name, count(*) from(
select resp1 as name from table1
union all
select resp2 as name from table1
union all
select resp3 as name from table1)
group by name
DCR
  • 14,737
  • 12
  • 52
  • 115
  • Just to make sure I understand the process, this UNION takes my three fields with X results eachs and makes them into one field with 3*X results then does the comparison. Is that correct? – Myles Apr 23 '15 at 16:40
  • yes that was the idea but union will not work as listed as it does not add dupplicates – DCR Apr 23 '15 at 16:49
  • 1
    You need UNION ALL to preserve duplicate names. – dnoeth Apr 23 '15 at 16:53
  • that's right! I adjusted the code accordingly and that should produce the desired outcome. Two issues: you may want to filter out blanks (add a where clause on each file) and you'll need to produce another file to join to to get names that aren't listed – DCR Apr 23 '15 at 17:03
  • Thanks for the effort. The other answer was more intuitive for me so I worked that angle. – Myles Apr 23 '15 at 17:57
1

One approach:

SELECT FullName, 
SUM(
       (select count(*) from qryItems where Responsible = FullName) + 
       (select count(*) from qryItems where Responsible2 = FullName) +
       (select count(*) from qryItems where Responsible3 = FullName)) 
FROM tblTeam
GROUP BY FullName
Myles
  • 176
  • 8
clweeks
  • 856
  • 7
  • 31
  • I tried this out and got a missing operator when the statement includes the "+" or the "rCounter". It works fine with what is between "(se...ame)" but attempting to add them in this way doesn't work. This seems like a good lead to explore. – Myles Apr 23 '15 at 17:10
  • Huh. I built my sample in SQL Server and assumed Access would parse the SQL the same. – clweeks Apr 23 '15 at 17:12
  • Also, @Myles, just so someone has said it, I think your table structure isn't ideal. I would prefer an extra table that's solely responsible for holding responsibilities. So it would have just a ticketID and teamMemberID column and each ticket could appear multiple times with different memberIDs. That would make this counting problem super-simple and also, I suspect, make lots of other things in the future work better. – clweeks Apr 23 '15 at 17:15
  • 1
    I had that but scrapped it because it caused too much complication between initial item entry and automating notification when responsibility changes. Everything is presently working except this one feature so it's something I will explore again when I start version 1.1. – Myles Apr 23 '15 at 17:37
  • The + signs are inside the Sum() declaration? – clweeks Apr 23 '15 at 17:57