Is it possible to do this query without creating a sub query? 2

[netsmurph]

I have one query that is a counting the number of duplicate records where the date, amount and accNo are the same.

The sql for this query is:

"SELECT Sum(1) AS [Count] " & _
"FROM " & m_sAnomaliesTable & _
" GROUP BY [amount] & [date] & [accno] " & _
"HAVING (((Sum(1))>1));"

But what i want to have is the total number of duplicate records (irrelevent of how many occurences there are)

To do this i have created a temp query def based on the above sql and used dsum function to roll up the count values to give a whole count.

Could anyone suggest how this could be down without creating a temp query - my current solution works but is not too elegent.

Is there any way to nest my first string into another count query?

Thanks in advance

Andrew

 

[Golom]

You could make it an in-line query.

"Select SUM(Temp.[DupCount]) As [Total Duplicates] " & _
"From " & _
"(" & _
"SELECT Count(*) AS [DupCount] " & _
"FROM " & m_sAnomaliesTable & " " & _
"GROUP BY [Amount], [Date], [AccNo] " & _
"HAVING Count(*) > 1 " & _
") As Temp "

 

[PHV]

SELECT Count(*) AS CountDistinct FROM (
SELECT Count(*) AS [Count] FROM yourTable
GROUP BY amount,[date],accno HAVING Count(*)>1
) AS C

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

 

[netsmurph]

Many thanks both of you they both work - knew it was possible, but couldnt get the syntax right!

Andrew