query a single row for duplicate fields in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why is this giving me two rows for 2 different contact_id's when the mailing
address for them is the same? When run alone, the third select brings back
only the first, which is what I need.

SELECT [contact].[mailing_salutation], [contact].[home_address_street],
[contact].[home_address_street2], [contact].[home_address_city],
[contact].[home_address_state], [contact].[home_address_zip]
FROM contact
WHERE (((contact.contact_id) In (select contact_id from (select
first(contact_id), mailing_salutation from contact group by
mailing_salutation))) AND ((contact.client_type) In ('AP','BP')));
 
I don't see any reason why it should not. You are restricting this by just the
salutation not by the entire address.

Also, I don't know why you've nested the query in the where clauses since what
you DO have could all be done in one subquery. What you do have is not what I
think you really want.

So, I think what you do have could be rewritten as
SELECT [contact].[mailing_salutation], [contact].[home_address_street],
[contact].[home_address_street2], [contact].[home_address_city],
[contact].[home_address_state], [contact].[home_address_zip]
FROM contact
WHERE contact.contact_id In
(SELECT first(contact_id)
FROM contact
GROUP BY mailing_salutation)
AND contact.client_type In ('AP','BP')

What you might actually want is

SELECT [contact].[mailing_salutation], [contact].[home_address_street],
[contact].[home_address_street2], [contact].[home_address_city],
[contact].[home_address_state], [contact].[home_address_zip]
FROM contact
WHERE contact.contact_id In
(SELECT first(contact_id)
FROM contact
WHERE contact.client_type In ('AP','BP')
GROUP BY [contact].[home_address_street],
[contact].[home_address_street2], [contact].[home_address_city],
[contact].[home_address_state], [contact].[home_address_zip])

If I got that correct, it should return one contact id for each uniquer street
address. Of course, this assumes that Contact_Id is a unique value in the
Contact table.
 
Back
Top