Removing duplicates

[joe]

I have a table whihc has Policy numbers and address related to the policy. I
want only one row per policy.
I tried setting the query properties to Unique values as well as unique
records. Both do not help

 

[Sprinks]

Hi, Joe.

Please post the query's SQL with Unique set to Yes. (View, SQL, cut and
paste).

Sprinks

 

[joe]

Sprinks

Thanks for you response, I am using Access 2000. in the design view of the
select query I have set the Unique values to yes.But this did not change the
SQL

I tried make table query too, anyway this is my query

SELECT DISTINCT [refer all].[Agent No], [refer all].HH, [refer all].[Client
Name], [refer all].[Mail street], [refer all].[Mail City], [refer all].[Mail
St], [refer all].[Mail Zip], [refer all].[Agent Name], [refer all].[Agent
Ph], [refer all].street, [refer all].l, [refer all].st, [refer
all].postalcode, [refer all].[Agent Lic]
FROM [refer all];

 

[Sprinks]

Joe,

Firstly, I don't see a Policy Number field in your query.

Secondly, the DISTINCT keyword, inserted when you set Unique Values to Yes,
eliminates duplicate rows based on the values of ALL of the fields in your
list. If one field is slightly different it will not be eliminated. To show
all the policy numbers just once, you would have:

SELECT DISTINCT [refer all].[PolicyNumber] FROM [refer all];

Perhaps if you tell me generically what the database is for and what you're
trying to accomplish with the query, I could help you further.

Sprinks

Sprinks

Thanks for you response, I am using Access 2000. in the design view of the
select query I have set the Unique values to yes.But this did not change the
SQL

I tried make table query too, anyway this is my query

SELECT DISTINCT [refer all].[Agent No], [refer all].HH, [refer all].[Client
Name], [refer all].[Mail street], [refer all].[Mail City], [refer all].[Mail
St], [refer all].[Mail Zip], [refer all].[Agent Name], [refer all].[Agent
Ph], [refer all].street, [refer all].l, [refer all].st, [refer
all].postalcode, [refer all].[Agent Lic]
FROM [refer all];

Hi, Joe.

Please post the query's SQL with Unique set to Yes. (View, SQL, cut and
paste).

Sprinks

 

[joe]

Sprinks

I was trying to keep it simple and that was the reason I mentione Policy
iwth multiple address.
As you see below I have Agent No, HH, Mailing adress and other agent details.
There are multiple rows for the same HH with different Mailing address. I
just want one row.
eg:

Agent No HH Client Name Mail street Mail City

29xxxx 0999999 MICHAEL Douglas 111111 SUM way LA
29xxxx 0999999 MICHAEL Douglas 222 MONT BLVD HERMOSA BCH
I just want the first row in the above
I realized the distinct only when I cut and paste it.

Thanks a bunch in advance

Joe,

Firstly, I don't see a Policy Number field in your query.

Secondly, the DISTINCT keyword, inserted when you set Unique Values to Yes,
eliminates duplicate rows based on the values of ALL of the fields in your
list. If one field is slightly different it will not be eliminated. To show
all the policy numbers just once, you would have:

SELECT DISTINCT [refer all].[PolicyNumber] FROM [refer all];

Perhaps if you tell me generically what the database is for and what you're
trying to accomplish with the query, I could help you further.

Sprinks

Sprinks

Thanks for you response, I am using Access 2000. in the design view of the
select query I have set the Unique values to yes.But this did not change the
SQL

I tried make table query too, anyway this is my query

SELECT DISTINCT [refer all].[Agent No], [refer all].HH, [refer all].[Client
Name], [refer all].[Mail street], [refer all].[Mail City], [refer all].[Mail
St], [refer all].[Mail Zip], [refer all].[Agent Name], [refer all].[Agent
Ph], [refer all].street, [refer all].l, [refer all].st, [refer
all].postalcode, [refer all].[Agent Lic]
FROM [refer all];

Hi, Joe.

Please post the query's SQL with Unique set to Yes. (View, SQL, cut and
paste).

Sprinks


:

I have a table whihc has Policy numbers and address related to the policy. I
want only one row per policy.
I tried setting the query properties to Unique values as well as unique
records. Both do not help