DISTINCT

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

Guest

I need to pull back a few field from a table. I need one of these fields to
be DISTINCT, at the moment Access is looking at the full record as DISTINCT
not just the one field that I want.

What am I doing wrong??
 
Dear Darren:

For me, at least, it is difficult to tell what it is you want this to do.

With respect to a row, DISTINCT simply eliminates duplicates. If two entire
rows are identical, it only shows that once.

I need to understand what kind of transformation of the data you expect this
to do.

Perhaps an example would be helpful.

Tom Ellison
 
Hi Tom,

The problem is my clients need to enter their info every time they are at
our offices. I need to pull back one copy of everyone’s details. What I want
to do is use their email address as a unique identifier, the current problem
is the distinct checks the entire record. So if a client fill in anything
differently, lets say the abbreviate their company name in the one record but
don’t in another then the distinct still sees both entries as unique even
though they have the same email address.
 
Dear Darren:

I think I'm catching on. But, if you have these two records, on what basis
do you want the computer to choose which one to show?

At this point, an example of what you have and what you want would be most
useful.

Tom Ellison
 
Hi Tom,

This is what i am using at the moment:

SELECT DISTINCT eMail, delname, surname, company
FROM [Delegate Info]
WHERE email<>" "
ORDER BY surname;

It is not to important to me which record is displayed as they should be
duplicates with only minor differences.
 
Dear Darren:

Well, I suppose you could do this:

SELECT eMail, MAX(delname), MAX(surname), MAX(company)
FROM [Delegate Info]
WHERE email <> " "
GROUP BY eMail
ORDER BY surname;

You could use MIN(), FIRST(), or LAST() instead of the MAX function.

Note that the result may not represent any existing row. The MAX function
will choose a value from one row for [delname] and from another row for
[surname], and yet another for [company] whenever that is where the maximum
values are found.

Does this work for your requirements?

Tom Ellison


Darren said:
Hi Tom,

This is what i am using at the moment:

SELECT DISTINCT eMail, delname, surname, company
FROM [Delegate Info]
WHERE email<>" "
ORDER BY surname;

It is not to important to me which record is displayed as they should be
duplicates with only minor differences.

Tom Ellison said:
Dear Darren:

I think I'm catching on. But, if you have these two records, on what
basis
do you want the computer to choose which one to show?

At this point, an example of what you have and what you want would be
most
useful.

Tom Ellison
Click to expand...
Click to expand...
 
Back
Top