How to remove redundant rows

[newsnet customer]

Hi

How do I remove redundant rows in a table?

Consider the following table:

tblMyTable

ID1 ID2
01 02
01 02
03 04
04 05
06 07

want

ID1 ID2
01 02
03 04
04 05
06 07

what I have tried:
1. manually removing redundant rows but got cramps cos i have a very large table (~10,000 rows).
2. set ID1 as unique but Access complained that there were duplicates so would not let me. is there a way to force access to do this and therefore automatically remove the redunt rows for me... that would be the easiest way i think.

Help would be much appreciated.

Cheers,
ST

 

[Douglas J Steele]

You can create a query that only returns the unique rows. When you've got
the query open in design view, look at its properties. You'll see two
choices: Unique Values and Unique Records. Unique Values only worries about
the fields that are actually in the query, which Unique Records worries
about all of the fields.

You can either leave the data in the table and use the query rather than the
table (although this would mean that you won't be able to update the data),
or you can use the query to populate a new table, and replace the old table
once you're done.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi

How do I remove redundant rows in a table?

Consider the following table:

tblMyTable

ID1 ID2
01 02
01 02
03 04
04 05
06 07

want

ID1 ID2
01 02
03 04
04 05
06 07

what I have tried:
1. manually removing redundant rows but got cramps cos i have a very large
table (~10,000 rows).
2. set ID1 as unique but Access complained that there were duplicates so
would not let me. is there a way to force access to do this and therefore
automatically remove the redunt rows for me... that would be the easiest way
i think.

Help would be much appreciated.

Cheers,
ST

 

[newsnet customer]

You can create a query that only returns the unique rows. When you've got

the query open in design view, look at its properties. You'll see two
choices: Unique Values and Unique Records. Unique Values only worries about
the fields that are actually in the query, which Unique Records worries
about all of the fields.

You can either leave the data in the table and use the query rather than the
table (although this would mean that you won't be able to update the data),
or you can use the query to populate a new table, and replace the old table
once you're done.

How you create a query that only returns the unique row?
If the query is done right then why I then look at properties and select
either unique values or unique rows.
I think I want unique rows.
This is getting confusing.

Help please.

ST

 

[Douglas J Steele]

How you create a query that only returns the unique row?
If the query is done right then why I then look at properties and select
either unique values or unique rows.
I think I want unique rows.
This is getting confusing.

Have you tried setting the Unique Rows property and then running your query?
Does it give you what you want?