By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,570 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,570 IT Pros & Developers. It's quick & easy.

Multiple occurance of same record

P: n/a
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
Share this Question
Share on Google+
4 Replies

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.