what's wrong with this sql statment? (uses "SELECT-IN")

[Bob]

running access 2000; I have a contacts table with name address phone
etc...

I'm trying to make a "find duplicates" query work, and am failing.
When I try to execute, i get an error message similiar to: "tried to
execute a query...expression...part of an aggregate function"

Here's the concept of what I want to do:
I'm looking for duplicate names. If I do JUST that - no problem. I get
a list of names that match one another; some of the names have null
addresses, some don't.

Here's what I want to add to this query:
If both of the records with a duplicated name, has a non-null address
field, AND those addresses do not match one another - then this isn't
REALLY a duplicate, and I don't want to see either record.

Here's how I've attempted to do this:

SELECT T_contacts.contact_id, T_contacts.last_name,
T_contacts.first_name, T_contacts.addr
FROM T_contacts
WHERE
(
(Q_patients.last_name)
In (
SELECT [last_name] FROM [T_contacts] As Tmp GROUP BY [last_name],
[first_name]
HAVING Count(*)>1 And [first_name] = [T_contacts].[first_name]
AND
( ([addr] <> '') and ([T_contacts].[addr] <> '') and [addr] =
[T_contacts].[addr] )
)
);


Could someone help me understand what the problem is with this
statement?
Or - is there a better way to do what I want?

TIA - Bob

 

[Dale Fye]

Start with a query that looks like:

SELECT Last_Name, First_Name, _
NZ(Addr, "") as Address, _
Count(*) as DupCount
FROM yourTable
GROUP BY Last_Name, First_Name, NZ(Addr, "")
HAVING Count(*) > 1

Then save this query and join it to your table on the Last_Name, First_Name,
and the computed field.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Bob]

Dale;

TX so much for your reply...
I'm gonna have to play with this a bit... interesting idea, or sortof
twisting the query around...
this specific one won't quite work as is, because it doesn't allow for
showing the contact_id - but like I said; I'll play with this a bit,
and figure out the best way to extrapolate that from subsequent
queries....

Bob

Start with a query that looks like:

SELECT Last_Name, First_Name, _
NZ(Addr, "") as Address, _
Count(*) as DupCount
FROM yourTable
GROUP BY Last_Name, First_Name, NZ(Addr, "")
HAVING Count(*) > 1

Then save this query and join it to your table on the Last_Name, First_Name,
and the computed field.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

running access 2000; I have a contacts table with name address phone
etc...

I'm trying to make a "find duplicates" query work, and am failing.
When I try to execute, i get an error message similiar to: "tried to
execute a query...expression...part of an aggregate function"

Here's the concept of what I want to do:
I'm looking for duplicate names. If I do JUST that - no problem. I get
a list of names that match one another; some of the names have null
addresses, some don't.

Here's what I want to add to this query:
If both of the records with a duplicated name, has a non-null address
field, AND those addresses do not match one another - then this isn't
REALLY a duplicate, and I don't want to see either record.

Here's how I've attempted to do this:

SELECT T_contacts.contact_id, T_contacts.last_name,
T_contacts.first_name, T_contacts.addr
FROM T_contacts
WHERE
(
(Q_patients.last_name)
In (
SELECT [last_name] FROM [T_contacts] As Tmp GROUP BY [last_name],
[first_name]
HAVING Count(*)>1 And [first_name] = [T_contacts].[first_name]
AND
( ([addr] <> '') and ([T_contacts].[addr] <> '') and [addr] =
[T_contacts].[addr] )
)
);

Could someone help me understand what the problem is with this
statement?
Or - is there a better way to do what I want?

TIA - Bob