How to generate a unique list based on more than one field?

[Amit]

Hi,

I'd like to get a unique list of organizations, based on
the organization name AND organization address.

1. Org A, Address 1 (location 1)
2. Org A, Address 2 (location 2)

Right now, the table has multiple entries for 1. & 2. I'd
like to get rid of the duplicates based on (org name + org
address). I know how to do this when there is one field,
using DISTINCT, but am not sure how to do this for more
than one field.

SELECT orgName, orgAddress
FROM tblOrg
WHERE ....???

Thanks for any help.

-Amit

 

[Graham Mandeno]

Hi Amit

You still use DISTINCT:

SELECT DISTINCT orgName, orgAddress ...

 

[Amit]

Hi Graham,

Thanks. That worked.

But, if I want to select more fields than these two, but
keep it unique based on two fields (orgName and
orgAddress), how will I do that? If I use DISTINCT, then
it will apply it to all the fields, but I want DISTINCT to
be applied to only those two fields.

-Amit

-----Original Message-----
Hi Amit

You still use DISTINCT:

SELECT DISTINCT orgName, orgAddress ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I'd like to get a unique list of organizations, based on
the organization name AND organization address.

1. Org A, Address 1 (location 1)
2. Org A, Address 2 (location 2)

Right now, the table has multiple entries for 1. & 2. I'd
like to get rid of the duplicates based on (org name + org
address). I know how to do this when there is one field,
using DISTINCT, but am not sure how to do this for more
than one field.

SELECT orgName, orgAddress
FROM tblOrg
WHERE ....???

Thanks for any help.

-Amit

.

 

[Graham Mandeno]

Hi Amit

I'm not sure what you're getting at.

Are you saying that if you have two records with fields A,B,X and A,B,Y,
then you want to see only one of them?

If so, then you must use a GROUP BY query (click the Greek "Sigma" on the
toolbar). Select your first two fields and choose "Group by" on the Total
row, then for the third field, choose Min, Max, or First (if you don't care
which value is returned).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks. That worked.

But, if I want to select more fields than these two, but
keep it unique based on two fields (orgName and
orgAddress), how will I do that? If I use DISTINCT, then
it will apply it to all the fields, but I want DISTINCT to
be applied to only those two fields.

-Amit

-----Original Message-----
Hi Amit

You still use DISTINCT:

SELECT DISTINCT orgName, orgAddress ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I'd like to get a unique list of organizations, based on
the organization name AND organization address.

1. Org A, Address 1 (location 1)
2. Org A, Address 2 (location 2)

Right now, the table has multiple entries for 1. & 2. I'd
like to get rid of the duplicates based on (org name + org
address). I know how to do this when there is one field,
using DISTINCT, but am not sure how to do this for more
than one field.

SELECT orgName, orgAddress
FROM tblOrg
WHERE ....???

Thanks for any help.

-Amit

.

 

[Amit]

Graham,

Sorry for the confusion. Not enough caffeine, and not
thinking clearly. You can disregard my earlier post, as it
is non-sensical, and thanks again for your helpful
responses.

-amit

-----Original Message-----
Hi Amit

I'm not sure what you're getting at.

Are you saying that if you have two records with fields A,B,X and A,B,Y,
then you want to see only one of them?

If so, then you must use a GROUP BY query (click the Greek "Sigma" on the
toolbar). Select your first two fields and choose "Group by" on the Total
row, then for the third field, choose Min, Max, or First (if you don't care
which value is returned).

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks. That worked.

But, if I want to select more fields than these two, but
keep it unique based on two fields (orgName and
orgAddress), how will I do that? If I use DISTINCT, then
it will apply it to all the fields, but I want DISTINCT to
be applied to only those two fields.

-Amit

-----Original Message-----
Hi Amit

You still use DISTINCT:

SELECT DISTINCT orgName, orgAddress ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I'd like to get a unique list of organizations, based on
the organization name AND organization address.

1. Org A, Address 1 (location 1)
2. Org A, Address 2 (location 2)

Right now, the table has multiple entries for 1. & 2. I'd
like to get rid of the duplicates based on (org name

+
org

address). I know how to do this when there is one field,
using DISTINCT, but am not sure how to do this for more
than one field.

SELECT orgName, orgAddress
FROM tblOrg
WHERE ....???

Thanks for any help.

-Amit


.

.