Find where the same value occurs more than once (1 Viewer)

[DenisCooper]

Say i have a table which contains payment information for various people - similar to this

Person Pay Date Pay Amount
User1 01/01/13 £10
User2 01/01/13 £10
User1 10/01/13 £0.00
User1 15/01/13 £0.00

I need to create a query which shows me only results for each person who has paid £0.00 more then once...

Thanks
Denis

 

[Brianwarnock]

You will need 2 queries
q1
SELECT Count(person) AS CountOfperson, person
FROM yourtable
WHERE pay_amount = 0
GROUP BY person
HAVING Count(person) >1;

q2 joins this back to the table
Select yourtable.*
From Q2 inner join yourtable on Q2.person = yourtable.person
where yourtable.pay_amount = 0


You will notivce that I have not used spaces in my names, they are a no no and will cause you heart ache with syntax, use only alpha numerics and the underscore in all oblect names.

Brian

 

[ypma]

Another way to achieve this is by using a duplicate query eg
#SELECT Test.AmmoutPaid, Test.LastName, Test.ID
FROM Test
WHERE (((Test.AmmoutPaid) In (SELECT [AmmoutPaid] FROM [Test] As Tmp GROUP BY [AmmoutPaid],[LastName] HAVING Count(*)>1 And [LastName] = [Test].[LastName] And [AmmoutPaid] = 0.00)))
ORDER BY Test.AmmoutPaid, Test.LastName;#

Might not be as per the book but appears to work
Test is the name of the Table
Fellow user not a programmer

 

[Brianwarnock]

I use sub queries when forced to, as in ranking , but prefer stacked queries otherwise as easier to check and debug, but each to their own.

Brian

 

[DenisCooper]

great - thanks - seems to have done the trick