How to remove duplicates from Excel while retaining data for relationships in Access?

[N Bru]

Newby here so apologies if my terminology is off...

I have a single Excel worksheet that includes a lot of repeated data values (though each row is unique) that I'd like to move into Access.



Basically the worksheet contains BusinessName, ClientContact, StaffAssigned, Event1, Event2, Event3, (and about 15 other columns).

I eventually want to set up the Access relationships where one BusinessName may have many ClientContacts who each have attended many Events. At the same time the StaffAssigned may be responsible for many BusinessNames.

I have no SQL experience so was hoping to just create separate tables, create primary keys and join tables to create relationships, but it seems I would have to manually manipulate the spreadsheet to match IDs before being able to import into tables.

Any ideas are greatly appreciated!

[June7]

The spreadsheet is not a normalized data structure so this won't be a simple import.

Getting the event fields into normalized structure will probably involve a UNION query.

If you want to use autonumber field as primary key that will require some updating of dependent tables after the import and after data is in normalized tables.

Right now it appears the only common identifier is the BusinessName. If this is not consistently spelled throughout the spreadsheet, need to clean up before import.

[recyan]

but it seems I would have to manually manipulate the spreadsheet to match IDs before being able to import into tables.

Would suggest getting over with the designing of the tables & relationships first.
Depending on the number of records, importing from excel in to access, though tedious, is definitely possible & as you pointed out, might require a bit of manipulation. I hope the data in your Excel is standardized, else that would be the 1st thing, even before the design phase, you will have to undertake.

Thanks