Find duplicates

[accessma]

I've got this query.

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] As Tmp GROUP BY [GrossAmt] HAVING Count(*)>1 )))
ORDER BY ApTest.[GrossAmt];

Which works fine but I I want to include negative amounts as being a duplicate too. So $100.00 and ($100.00) I want to consider a duplicate.

 

[Galaxiom]

Try this:
GROUP BY Abs([GrossAmt])

 

[accessma]

Changed it to this:

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] As Tmp GROUP BY Abs ([GrossAmt]) HAVING Count(*)>1 )))
ORDER BY ApTest.[GrossAmt];

Now it throws an error saying it cant find GrossAmt

 

[accessma]

I should clarify my error I get, says I am trying to execute a query that does not include the specified expression 'GrossAmt' as part of an aggregate function.

 

[accessma]

I got this far but it says I have an extra ( near my Abs part of the query. Microsoft and Google help not that much help. Can anyone see the syntax error?

SELECT ApTest.InvNum, ApTest.PoNum, ApVenTest.VenNum, ApVenTest.VenName, ApTest.GrossAmt, Abs([GrossAmt]) AS Expr1
FROM ApVenTest INNER JOIN ApTest ON ApVenTest.VenNum = ApTest.VenNum
GROUP BY ApTest.GrossAmt;

Access 2003 BTW.

 

[Galaxiom]

You have aliased the table as tmp so the main query does not see an object called ApTest.
Just take out the red bit.

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] [COLOR=red]As Tmp[/COLOR] GROUP BY Abs ([GrossAmt]) HAVING Count(*)>1 )))
ORDER BY ApTest.[GrossAmt];

 

[accessma]

That threw the followinf error:

"You tried to execute a query that does not include the specified expression "GrossAmt" as part of an aggragate.

Here is what ran:

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] GROUP BY Abs ([GrossAmt]) HAVING Count(*)>1 )))
ORDER BY ApTest.[GrossAmt];

 

[Galaxiom]

Add [GrossAmt] to the Group By list.

 

[accessma]

Confused, its already being grouped by Abs ([GroupBy])? Are you saying put it somewhere else?

 

[Galaxiom]

Abs([GrossAmt]) produces another field that is not displayed. For reasons only known to Microsoft, Access demands that every field in an Aggregate is grouped in some way. Hence you must group by both fields. Though grouping by all fields seem odd, the point is to get the HAVING clause.

You should get two or more records for each Abs value. Also change you Order By to first by the Abs field then the GrossAmt so they are displayed together.

You could alias the Abs field so Access does not have to recalculate it for the Order By clause.

 

[accessma]

Would you alter my query to show me what is correct? Its the only way I will get it. Thanks

 

[accessma]

Here is what I changed it to:

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] GROUP BY Abs ([GrossAmt]), [GrossAmt] HAVING Count(*)>1 )))
ORDER BY  Abs ([GrossAmt]), ApTest.[GrossAmt];

Runs and finds dups but no "-" sign showing in GrossAmt. That is what I need to show.

 

[Galaxiom]

Must surely be close.
Try this with the Having on the Abs field.

SELECT ApTest.[GrossAmt], ApTest.[VenNum]
FROM ApTest
WHERE (((ApTest.[GrossAmt]) In (SELECT [GrossAmt] FROM [ApTest] GROUP BY [GrossAmt], Abs([GrossAmt]) HAVING Count(*)>1 )))
ORDER BY Abs ([GrossAmt]), ApTest.[GrossAmt];

 

[accessma]

Thanks but sorry no. Same result. You think the Abs val is hiding the minus sign?

 

[Galaxiom]

Abs() give the Absolute (unsigned) value. However you are selecting [GrossAmt] so it should return both negative and positive values.

 

[accessma]

You and me both....