How do I remove records in which a field value is duplicated in another record?

M

Matt Meserve

I'm running a query that outputs customer names, addresses, phone #, etc.
from our customer database.

Due to the nature of the business, there are many entries in which both
spouses, partners, etc. are entered separately into the database. So, my
query results list each spouse as a separate record (2 different people but
with the same mailing address in each record).

I'll be using these query results as a reference for who I want to send
mailers to but I only want to send one mailer per household.

Is there a way I can tell my query to only return one record if there is
more than one record with the same address listed in the address field?

For example:

Record 1 = Joe Smith, 18500 S. Maple Lane, Pleasantville, CA
Record 2 = Mary Smith, 18500 S. Maple Lane, Pleasantville, CA

Can I tell the query to only return Joe Smith but not Mary Smith in the
results since they both have the same addres? Or can I requery the results
to accomplish this?

Thanks!
Matt
 
B

Brett Collings [429338]

Use SELECT DISTINCTROW and the address field. Not ideal but it should
work

Brett

I'm running a query that outputs customer names, addresses, phone #, etc.
from our customer database.

Due to the nature of the business, there are many entries in which both
spouses, partners, etc. are entered separately into the database. So, my
query results list each spouse as a separate record (2 different people but
with the same mailing address in each record).

I'll be using these query results as a reference for who I want to send
mailers to but I only want to send one mailer per household.

Is there a way I can tell my query to only return one record if there is
more than one record with the same address listed in the address field?

For example:

Record 1 = Joe Smith, 18500 S. Maple Lane, Pleasantville, CA
Record 2 = Mary Smith, 18500 S. Maple Lane, Pleasantville, CA

Can I tell the query to only return Joe Smith but not Mary Smith in the
results since they both have the same addres? Or can I requery the results
to accomplish this?

Thanks!
Matt
Click to expand...

Cheers,
Brett
 
M

Matt Meserve

Hmmm. This looks like an SQL statement. Would I need to include this in
the actual SQL statement behind the query or is there a way to do this using
the simple query design grid with tables in the upper pain?

Thanks,
Matt
 
M

Michel Walsh

Hi,



SELECT LAST(FirstName), Address
FROM myTable
GROUP BY Address


Hoping it may help,
Vanderghast, Access MVP
 
M

Matt Meserve

I'm using this SQL statement in the Criteria field of the Address field
column in Design View but it still returns each record that has the same
address.

In (SELECT DISTINCT [Field105] FROM [1thru6])

Address Field=[Field105]
Table Name=[1thru6]


Any suggestions?

Thanks,
Matt
 
M

Michel Walsh

Hi,


The DISTINCT is inside the membership, IN.


SELECT *
FROM somewhere
WHERE x IN( 4, 5, 4, 5, 5, 5, 5, 4, 5, 6)


is the same as

SELECT *
FROM somewhere
WHERE x IN( 4, 5, 6)




You removed DISTINCT in the list of allowable membership, not in the list
that compared itself to it:



SELECT DISTINCT *
FROM somewhere
WHERE x IN( 4, 5, 6)



would.




Hoping it may help,
Vanderghast, Access MVP


Matt Meserve said:
I'm using this SQL statement in the Criteria field of the Address field
column in Design View but it still returns each record that has the same
address.

In (SELECT DISTINCT [Field105] FROM [1thru6])

Address Field=[Field105]
Table Name=[1thru6]


Any suggestions?

Thanks,
Matt



Michel Walsh said:
Hi,



SELECT LAST(FirstName), Address
FROM myTable
GROUP BY Address


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Perhaps a Totals query where you group on all the address data and use First
against the name field. Note that my wife might be upset if you addressed the
mail to me only and we were both members, but that is another problem.

SELECT First([ClientName]) as AName,
[StreetAddress],
[City],
[State],
[ZIP Code]
FROM [YourTable]
GROUP BY [StreetAddress],
[City],
[State],
[ZIP Code]
 
M

Matt Meserve

Hmmm.

Here is the SQL side of my query as it stands now. Still not working.
Please take a look at it.

SELECT [1thru6].[-], [1thru6].Field79, [1thru6].Field193, [1thru6].Field205,
[1thru6].Field104, [1thru6].Field105, [1thru6].Field107, [1thru6].Field106,
[1thru6].Field108, [1thru6].[CLOSE PN DUE TO], [1thru6].STATUS
FROM 1thru6
WHERE ((([1thru6].Field105) In (SELECT DISTINCT [Field105] FROM [1thru6])));

Table = 1thru6
Address Field = Field105

It is still returning all of my records. What do I need to change.

Thanks!
Matt









John Spencer (MVP) said:
Perhaps a Totals query where you group on all the address data and use
First
against the name field. Note that my wife might be upset if you addressed
the
mail to me only and we were both members, but that is another problem.

SELECT First([ClientName]) as AName,
[StreetAddress],
[City],
[State],
[ZIP Code]
FROM [YourTable]
GROUP BY [StreetAddress],
[City],
[State],
[ZIP Code]

Matt said:
I'm running a query that outputs customer names, addresses, phone #, etc.
from our customer database.

Due to the nature of the business, there are many entries in which both
spouses, partners, etc. are entered separately into the database. So, my
query results list each spouse as a separate record (2 different people
but
with the same mailing address in each record).

I'll be using these query results as a reference for who I want to send
mailers to but I only want to send one mailer per household.

Is there a way I can tell my query to only return one record if there is
more than one record with the same address listed in the address field?

For example:

Record 1 = Joe Smith, 18500 S. Maple Lane, Pleasantville, CA
Record 2 = Mary Smith, 18500 S. Maple Lane, Pleasantville, CA

Can I tell the query to only return Joe Smith but not Mary Smith in the
results since they both have the same addres? Or can I requery the
results
to accomplish this?

Thanks!
Matt
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

So, guesses are in order.

Nice field names. It makes things really clear which fields you are trying to
select.

Which ones are the name fields? Assuming for ILLUSTRATION purposes only that the
first two contain the names, I would try

SELECT First([1thru6].[-]), First([1thru6].Field79),
[1thru6].Field193, [1thru6].Field205,
[1thru6].Field104, [1thru6].Field105, [1thru6].Field107, [1thru6].Field106,
[1thru6].Field108, [1thru6].[CLOSE PN DUE TO], [1thru6].STATUS
FROM 1thru6
GROUP BY
1thru6].Field193, [1thru6].Field205,
[1thru6].Field104, [1thru6].Field105, [1thru6].Field107, [1thru6].Field106,
[1thru6].Field108, [1thru6].[CLOSE PN DUE TO], [1thru6].STATUS

You were basically getting a list of all unique addresses in the database and
then matching to that. If you have 123 Main in the table 18 times, the subquery
will return 123 Main once, but all 18 records that have 123 Main in them will
match the unique value.

Note that the above may very well still give you duplicates. I have no clue
what the structure of your table looks like or which fields you really need in
the query.

Matt said:
Hmmm.

Here is the SQL side of my query as it stands now. Still not working.
Please take a look at it.

SELECT [1thru6].[-], [1thru6].Field79, [1thru6].Field193, [1thru6].Field205,
[1thru6].Field104, [1thru6].Field105, [1thru6].Field107, [1thru6].Field106,
[1thru6].Field108, [1thru6].[CLOSE PN DUE TO], [1thru6].STATUS
FROM 1thru6
WHERE ((([1thru6].Field105) In (SELECT DISTINCT [Field105] FROM [1thru6])));

Table = 1thru6
Address Field = Field105

It is still returning all of my records. What do I need to change.

Thanks!
Matt

John Spencer (MVP) said:
Perhaps a Totals query where you group on all the address data and use
First
against the name field. Note that my wife might be upset if you addressed
the
mail to me only and we were both members, but that is another problem.

SELECT First([ClientName]) as AName,
[StreetAddress],
[City],
[State],
[ZIP Code]
FROM [YourTable]
GROUP BY [StreetAddress],
[City],
[State],
[ZIP Code]

Matt said:
I'm running a query that outputs customer names, addresses, phone #, etc.
from our customer database.

Due to the nature of the business, there are many entries in which both
spouses, partners, etc. are entered separately into the database. So, my
query results list each spouse as a separate record (2 different people
but
with the same mailing address in each record).

I'll be using these query results as a reference for who I want to send
mailers to but I only want to send one mailer per household.

Is there a way I can tell my query to only return one record if there is
more than one record with the same address listed in the address field?

For example:

Record 1 = Joe Smith, 18500 S. Maple Lane, Pleasantville, CA
Record 2 = Mary Smith, 18500 S. Maple Lane, Pleasantville, CA

Can I tell the query to only return Joe Smith but not Mary Smith in the
results since they both have the same addres? Or can I requery the
results
to accomplish this?

Thanks!
Matt
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

showing records where one field is duplicated and another is not 2
How assume wildcards in a query criteria 3
How to create a table from a find duplicates query 1
How do I copy a record from one query to another? 4
How do I calculate a value in a new record from the previous recor 2
Combine records with a common field into one single record 2
How do you make a Query that shows records even if a field is blan 5
Must I create a record for each value in a field in this table? 5

Top