Those aggregate query blues

[kupe]

I have a query that uses some of Mile-O-Phile’s code for removing duplicates from a table. “Expr1” is the ID that identifies the tables that each of the records comes from.

When I use the following code, Access says: “You tried to execute a query that does not include the specified expression Expr1 as part of an aggregate function.”

But I don’t want Expr1 to be changed in any way.

I have no more luck when I try to do it as a totals QBE. Does anyone know the secret to get a field passed a totals query without stipulating “group” or “sum” or whatever, please?

The code that isn’t working is:

SELECT [qryUnionJM].[Expr1], [qryUnionJM].[ShortUrl], Sum([qryUnionJM].[SumOfHits]) AS SumOfSumOfHits
FROM qryUnionJM
GROUP BY [qryUnionJM].[ShortUrl]
HAVING (((Sum(qryUnionJM.SumOfHits))>2));

 

[EMP]

>
"Expr1" is the ID that identifies the tables that each of the records comes from.
<

If it doesn't matter which tables the ShortUrl's come from, you can delete the [qryUnionJM].[Expr1], from the SQL statement.

 

[kupe]

Thanks, EMP, but Expr1 is vital. It is important that the tables the IDs come from is known. Otherwise, you're right, I could just forget Expr1.

 

[EMP]

To show the Expr1's, you'll have to Group By it as well.

 

[kupe]

And there's the problem. If I group Expr1, the query won't group "ShortUrl" and add up - which is the way I remove duplicates.

(In this particular batch of 40,452 records, 4153 are duplicates. Each record is made up of a url and a number. The query is supposed to add the numbers of each duplicate to the original and then be removed. It works really well without the ID field - Expr1. But I need to include IDs and that makes it, well, challenging and perhaps even character building.)

 

[kupe]

Thanks, EMP, but that doesn't do it. However, the answer arrived on Saturday night when I was about to slip a noose round this unworthy neck. "Use Min or Max," a Gates-like cry echoed inside this reeling head. I clambered down from the stool, tried it, and yes, it seems to work. Cheers.