Use of DISINCT query or alternative method

  • Thread starter Thread starter K Heath
  • Start date Start date
K

K Heath

Hello all

I am having issues with the DISTINCT keyword in access97 -
hoping someone can offer some assistance.

I have a table called "data". I want to select
fields "email", "title", "initial" and "last name" from
the table. Duplicates exist such that the is the
same, but the rest of the fields aren't. I need records
with unique email addresses - IE only 1 of each duplicate
(email) record in addition to all the unduplicated records.

I realise that i can't use DISTINCT, DISTINCTROW,
UniqueRecord Property or UniqueValue property in my
sql/query because they will form a unique record given all
the output fields of the query.

It doesn't matter which record of the duplicate record set
is output, just that only 1 needs to be output.

How can I create this query?

Thanks in advance.

Kris
 
You might try a query whose SQL looks something like this:

SELECT
,
First([title]) AS [title],
First([initial]) AS [initial],
First([last name]) AS [last name]
FROM
[data]
GROUP BY
[email]

This will return a list of distinct email addresses, each one with a
combination of title, initial and last name arbitrarily selected from those
with the email address.
 
Thanks very much brian, that's worked a treat!

Regards

Kris
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
,
First([title]) AS [title],
First([initial]) AS [initial],
First([last name]) AS [last name]
FROM
[data]
GROUP BY
[email]

This will return a list of distinct email addresses, each one with a
combination of title, initial and last name arbitrarily selected from those
with the email address.


[QUOTE]
Hello all

I am having issues with the DISTINCT keyword in access97 -
hoping someone can offer some assistance.

I have a table called "data". I want to select
fields "email", "title", "initial" and "last name" from
the table. Duplicates exist such that the [email] is the
same, but the rest of the fields aren't. I need records
with unique email addresses - IE only 1 of each duplicate
(email) record in addition to all the unduplicated records.

I realise that i can't use DISTINCT, DISTINCTROW,
UniqueRecord Property or UniqueValue property in my
sql/query because they will form a unique record given all
the output fields of the query.

It doesn't matter which record of the duplicate record set
is output, just that only 1 needs to be output.

How can I create this query?

Thanks in advance.

Kris[/QUOTE]


.
[/QUOTE]
Click to expand...
 
Back
Top