CODEWITHSUNDEEP
sqlms-accesscountms-access-2010
Adam Merrifield
Adam Merrifield

Reputation: 10407

Access SQL Query Returning Wrong Value for Count Field

I'm trying to make a SQL query that get's the Customer's name plus a column of c which is the Count of how many rows that connect to Referrals with Customers.[Customer ID] = Referrals.[Referred From] and Referrals.[Used Our Service] = True

And a second column with of c2 which is the Count of how many rows that connect to Referrals with Customers.[Customer ID] = Referrals.[Referred From] and Referrals.[Valid Referral] = True

This query works fine but returns the wrong number in the c2 column. It returns 2 when it should be 1.

SELECT 
Customers.[Customer ID], Customers.[First Name], Customers.[Last Name], Count(r.[Valid Referral]) AS c, Count(rr.[Used Our Service]) AS c2
FROM (Customers 
INNER JOIN Referrals r ON Customers.[Customer ID] = r.[Referred From] AND (r.[Valid Referral])=True)
INNER JOIN Referrals rr ON Customers.[Customer ID] = rr.[Referred From] AND (rr.[Used Our Service])=True
WHERE (r.[Valid Referral])=True
GROUP BY Customers.[Customer ID], Customers.[First Name], Customers.[Last Name]

Customers Table:

enter image description here

Referrals Table:

enter image description here

Result Table:

enter image description here

Upvotes: 0

Views: 889

Answers (1)

cha
cha

Reputation: 10411

I'd recommend that instead of joining the same table twice you use SUM together with Iif, like this:

SELECT 
Customers.[Customer ID], 
Customers.[First Name], 
Customers.[Last Name], 
SUM(Iif(r.[Valid Referral] = True, 1, 0)) AS c, 
Sum(Iif(r.[Used Our Service] = True, 1, 0)) AS c2
FROM Customers 
INNER JOIN Referrals r ON Customers.[Customer ID] = r.[Referred From] 
WHERE (r.[Valid Referral])=True
GROUP BY Customers.[Customer ID], Customers.[First Name], Customers.[Last Name]

Upvotes: 1

Related Questions