Eliminating Duplicates from a Query Access 2007

[WSR]

I've run a query that captures the data set I want. Now I want to eliminate
Duplicate names and retain only the unique names.

These are text fields, can I do this in the Criteria in Design View?

Many thanks.

(B^>)-]=[

 

[Stefan Hoffmann]

hi,

I've run a query that captures the data set I want. Now I want to eliminate
Duplicate names and retain only the unique names.

These are text fields, can I do this in the Criteria in Design View?

Take a look at the Unique Values property of your query.


mfG
--> stefan <--

 

[golfinray]

Under query types, there is a find duplicates query that will find those for
you. You could then delete them, move them to another table, or whatever.

 

[John Spencer]

It may be a simple modification to your query. It would help if you posted
the SQL text of your query.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

IF you are talking about just the names, then you would add the DISTINCT
operator to the query. This combines records where the fields returned are
the same. So two records with all the same values get returned as just one row.

If you are using query design view to set up the query, you need to change the
Unique Values property of the query to Yes (True).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[WSR]

SELECT tbl_Investment_Commitments.[Investment Name],
tbl_Investment_Commitments.[Investment Type],
tbl_Investment_Commitments.[Date of Commitment]
FROM tbl_Investment_Commitments
WHERE (((tbl_Investment_Commitments.[Investment Type])="Fund") AND
((tbl_Investment_Commitments.[Date of Commitment])>#5/1/2008#));


Thanks for your reply. SQL above.

I'll try your 2nd solution for now.

Regards

It may be a simple modification to your query. It would help if you
posted the SQL text of your query.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

IF you are talking about just the names, then you would add the DISTINCT
operator to the query. This combines records where the fields returned
are the same. So two records with all the same values get returned as
just one row.

If you are using query design view to set up the query, you need to change
the Unique Values property of the query to Yes (True).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I've run a query that captures the data set I want. Now I want to
eliminate Duplicate names and retain only the unique names.

These are text fields, can I do this in the Criteria in Design View?

Many thanks.

(B^>)-]=[

 

[WSR]

That's the ticket!

Thanks

hi,


Take a look at the Unique Values property of your query.


mfG
--> stefan <--

 

[John Spencer]

This modification (adding the operator DISTINCT) to your query will combine
any rows that have the same values in the three fields you are displaying.

SELECT DISTINCT tbl_Investment_Commitments.[Investment Name],
tbl_Investment_Commitments.[Investment Type],
tbl_Investment_Commitments.[Date of Commitment]
FROM tbl_Investment_Commitments
WHERE (((tbl_Investment_Commitments.[Investment Type])="Fund") AND
((tbl_Investment_Commitments.[Date of Commitment])>#5/1/2008#));

And setting the Unique Value property does exactly that. It adds the DISTINCT
operator to the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SELECT tbl_Investment_Commitments.[Investment Name],
tbl_Investment_Commitments.[Investment Type],
tbl_Investment_Commitments.[Date of Commitment]
FROM tbl_Investment_Commitments
WHERE (((tbl_Investment_Commitments.[Investment Type])="Fund") AND
((tbl_Investment_Commitments.[Date of Commitment])>#5/1/2008#));


Thanks for your reply. SQL above.

I'll try your 2nd solution for now.

Regards

It may be a simple modification to your query. It would help if you
posted the SQL text of your query.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message