duplicate vlues

[Guest]

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.

 

[Smartin]

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.

Sure thing, but not an expression per se. Try SELECT DISTINCT.

 

[Guest]

I simply type that as the creteria?

Sure thing, but not an expression per se. Try SELECT DISTINCT.

 

[John W. Vinson]

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.

View the query's Properties (by right clicking the grey background of
the tables in the query design window) and set its Unique Values
property to Yes.

If you then look at the SQL view, you will see that it says

SELECT DISTINCT field, field, field...

just as Smartin suggests.

John W. Vinson [MVP]

 

[Guest]

the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

 

[Smartin]

the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

"Hiding records that have duplicates" is different than "showing
duplicate records only once." (^:

Speaking to the former, try creating an aggregate query to obtain a
Count on email1. In the criterion for this field place "1" (without
quotes).

 

[Guest]

Sorry, that didn't work.

the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

On Wed, 7 Feb 2007 17:33:00 -0800, sierralightfoot

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.

"Hiding records that have duplicates" is different than "showing
duplicate records only once." (^:

Speaking to the former, try creating an aggregate query to obtain a
Count on email1. In the criterion for this field place "1" (without
quotes).

 

[Smartin]

Sorry, that didn't work.

Please see below

the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

:

On Wed, 7 Feb 2007 17:33:00 -0800, sierralightfoot

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.

"Hiding records that have duplicates" is different than "showing
duplicate records only once." (^:

Speaking to the former, try creating an aggregate query to obtain a
Count on email1. In the criterion for this field place "1" (without
quotes).

Let's go back to SELECT DISTINCT.

Simple query:

Add the table that has emails
Add the email field, and nothing else
Optionally, sort this Ascending (makes visual detection of duplicates
easier)
Select View | Properties
In the Query Properties dialog, change "Unique Values" to Yes
Run

Is this what you are after?

 

[Guest]

I was aware of this option but it prevents me from using other fields in a
mail merge.

Sorry, that didn't work.

Please see below

sierralightfoot wrote:
the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

:

On Wed, 7 Feb 2007 17:33:00 -0800, sierralightfoot

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.
"Hiding records that have duplicates" is different than "showing
duplicate records only once." (^:

Speaking to the former, try creating an aggregate query to obtain a
Count on email1. In the criterion for this field place "1" (without
quotes).

Let's go back to SELECT DISTINCT.

Simple query:

Add the table that has emails
Add the email field, and nothing else
Optionally, sort this Ascending (makes visual detection of duplicates
easier)
Select View | Properties
In the Query Properties dialog, change "Unique Values" to Yes
Run

Is this what you are after?

 

[Smartin]

I was aware of this option but it prevents me from using other fields in a
mail merge.

If the other fields are "distinct" as well, feel free to add them. If
they are not, then we need help zeroing in on what makes a record
unique. Besides email address, what else do you need to see?

Sorry, that didn't work.

Please see below

:

sierralightfoot wrote:
the select distinct hides identical records. I want to hide records that have
a duplicate field, ie:[email1]

:

On Wed, 7 Feb 2007 17:33:00 -0800, sierralightfoot

I am using a query as a mail merge source. I have some e-mail address that
are duplicates and I want them to be maintained that way in the table. Is
there an expression I can use in the creteria of the query that says return
duplicate values only once.
"Hiding records that have duplicates" is different than "showing
duplicate records only once." (^:

Speaking to the former, try creating an aggregate query to obtain a
Count on email1. In the criterion for this field place "1" (without
quotes).

Let's go back to SELECT DISTINCT.

Simple query:

Add the table that has emails
Add the email field, and nothing else
Optionally, sort this Ascending (makes visual detection of duplicates
easier)
Select View | Properties
In the Query Properties dialog, change "Unique Values" to Yes
Run

Is this what you are after?