469,575 Members | 1,291 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

Multiple occurance of same record

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

May 24 '07 #1
4 1519
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 ***
May 24 '07 #2
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".

May 25 '07 #3
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
May 25 '07 #4
Not founf in Excel 98

Chuck
Was referring to Office 2007

May 25 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by David Warner | last post: by
4 posts views Thread by Jim Heavey | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.