Count Duplicates and then Add Rows 2

[chrsab]

Hello

I have being trying to solve this for days now and I have come to the decision that i need help, so here I am.

What I need is to find duplicates in one column and then add the contains of other columns. Example below:

What I have (In a table):

Name A B Money
Mr X Yes No £20
Mr X Yes No £20
Mr X No Yes £5
Ms Y No Yes £5

What I would like (As result of a query):

Name A B Money
Mr X 2 1 £45
Ms Y 0 1 £5

So from the table, it adds up, how many A's and then how many B's for each Name and calculates the money owed.

So Mr X had two A's displaying Yes and 1 B displaying No but all three rows added together to make £45.

Thanx for any help you can provide.

 

[DotNetGnat]

SELECT Name, COUNT(A), COUNT(B),SUM(Money)
FROM mytable GROUP BY Name

-DNG

 

[DotNetGnat]

i think you need to use the IIF() statement for counting Yes and NO...something like this...

Count(IIF(A='yes',1,0))

-DNG

 

[PHV]

Or if A and B are Boolean (or YesNo) fields:
SELECT Name, -Sum(A) AS Ayes, -Sum(B) AS Byes, Sum([Money] AS Total
FROM yourTable
GROUP BY Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886