Importing from Excel to Access

[Suzie~Wow]

I would like to import data from Excel (contains duplicates) into access.
When importing I would like access to remove the duplicates but after I set
the primary key I get an error that the info will not import. Please help. Am
I able to do this or do I have to write a query? If so how would I do that?
Thank you

 

[Golfinray]

Import the spreadsheet without trying to remove duplicates. Then use a find
duplicates query to find your duplicates. When you use the query wizard, one
option is duplicates. Use that option, bring your spreadsheet into the query,
and run it. It will find duplicates for you.

 

[Dale Fye]

Unfortunately, you will then have to decide which of the "duplicates" to keep
and which to delete. If you are checking for duplicates across all of the
fields in the entire record, this will not be too difficult. However, if you
are looking for duplicates based on just a couple of fields, you may find
that one record has some fields filled in and another has others, or that the
values in some of the non-duplicate fields are different (in which case,
which record do you keep or discard).

Good luck with this.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Suzie~Wow]

I did run a duplicate query now I want to delete them from the master table
how do I do that?

 

[Suzie~Wow]

What about an append query would I be able to remove the duplicates that way?

 

[Dale Fye]

You should just:

1. Copy your master table (just in case)
2. Run the duplicates query
3. In the query results, the duplicates will be shown as successive
records. All you should have to do is select the row you want to delete
(from each pair, you might even have triplicates), right click on the record
selector, and select the delete record option. This will delete the record
from the query and from the master table .

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Suzie~Wow]

I am trying to find a faster way to do that because I am dealing with over
3000 records. Any other suggestions

 

[Dale Fye]

Suzie,

there are a number of ways to do this, but it all comes down to the question
in my original post.

Are you identifying duplicates by those records that match every field in
each record, or only specific fields.

If it is every field, then you could create a query that contains all of
your fields, click the totals button, and group by all of the fields. Then
change the query to a make-table or append query to put the unique records in
a table.

If, however, it is not every field, and the fields that are not included in
the "duplicates" portion of the query are different, then how do you
determine which of the duplicate records to delete? You could use the same
query as above, but change the Totals row in the query to First, Last, Min,
Max, ... for each of the fields, but there is no telling what data you will
end up with in your final table.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Peter Hibbs]

Is this a one-off operation, that is you have a spreadsheet file that
you want to convert into an Access database. If so then have a look at
my Excel to Access 2000/XP Converter Program at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
which does that automatically for you. It will remove duplicates,
create related tables, link tables automatically, etc, etc.

If, on the other hand, you are trying to import Excel files to an
existing database on a regular basis then you should go with the other
replies you have already received.

HTH

Peter Hibbs.

 

[Ken Sheridan]

Assuming for this example that the duplicates are determined by one column,
MyDupColumn, first make sure that the imported table, MyTable, includes an
autonumber column, MyID. You can then remove the surplus duplicates with:

DELETE *
FROM MyTable AS T1
WHERE MyID <>
(SELECT MIN(MyID)
FROM MyTable AS T2
WHERE T2.MyDupColumn = T1.MyDupColumn);

If the duplication is determined by a combination of columns you'll need to
include them all when correlating the subquery e.g.

WHERE T2.MyDupColumn = T1.MyDupColumn
AND T2.SomeOtherColumn = T1.SomeOtherColumn
AND T2.YetAnotherColumn = T1.YetAnotherColumn

Each duplicated row apart from those with the lowest MyID value per set of
duplicates will be deleted.

Ken Sheridan
Stafford, England