Recently I've been migrating our databases from Corel Paradox to Microsoft Access and things have been going well overall, however I had a question about duplicate elimination.
When using the query wizard to create a duplicate check query, I want to be able to search for multiple fields, but I want the query to group everything in the order of a completely separate field (such as customer ID).
In particular this query uses OR as it's main operator. Right now my goal is to be able to do something along the lines of query [field1] [field2] while setting the group as value to [id].
So far when I try using a separate group value, I get an error saying I'm using a value not part of the aggregate function.
In (SELECT [Zip] FROM [HOUS0309] As Tmp GROUP BY [Zip],[Last], Right ([First], 2), Right ([Street], 2) HAVING Count(*)>1 And [Last] = [HOUS0309].[Last] And [First] = [HOUS0309].[First] And [Street] = [HOUS0309].[Street])