Multiple occurance of same record

[Chris]

Hi,

I have imported a spreadsheet into an access databsae and have noticed
that there are multiple occurances of exactly the same record. I
would like to know if there is a way of deleting the muliple
occurances of the record and leave it so that there is only one record
of each occurance remaining.

I am aware that there is a feature in the query wizard to delete
multiple occurances but I dont want to delete all occurances - just
ensure that there is only one of each.

e.g

column A Column B
column A Column B
column A Column B

I would like a query that would delete two of the records but leave
one remianing.

Any help would be greatly appreciated,
Chris

[Rich P]

Hi Chris,

Here is what you can do - write a query that selects the first row from
each group of duplicate rows and insert those unique records into a new
table. The new table will contain only the unique rows.

Here is the simplest way to get only one row from a group of duplicate
rows:

Select Distinct * From tblx

and here is how to insert those rows into a new table

Select * Into NewTbl From
(Select Distinct * from tblx) t1

You don't have to create NewTbl. The Sql statement will create it
automatically. This is a Make Table query. You can rename tblx to the
name of your actual table. And t1 is an alias for the subquery that is
your table of unique rows that you are going to insert into NewTbl. The
alias (an alias) is required when using a subquery to define a table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Matuag]

I came across similar situation. So try this if it suits in your case.

Delete duplicate records in Excel before importing it in Access. Excel
has a feature called "remove duplicates".

[Chuck]

On 25 May 2007 04:06:34 -0700, Matuag <ma****@gmail.comwrote:

>I came across similar situation. So try this if it suits in your case.

Delete duplicate records in Excel before importing it in Access. Excel
has a feature called "remove duplicates".

Not founf in Excel 98

Chuck

[Matuag]

Not founf in Excel 98

Chuck

Was referring to Office 2007