Importing from Excel to Access

  • Thread starter Thread starter Grace
  • Start date Start date
G

Grace

Hello, I need to import data from Excel into an Access
database. My problem is that the data in Excel is
not "Normalized". For example, one record in Excel
contains one-to-many relationships. See example below for
one record:

record 1: [ClaimNumber1], [ClaimName], [AssignedAtty],
[InjuryCode1], [InjuryCode2], [InjuryCode3],
[InjuryCode4], ...

There can be up to 25 Injury Codes per claim. In Access I
have a table that stores the Claim information where the
[ClaimNumber] is the primary key] and another table that
stores the [InjuryCodes] per [ClaimNumber] where the
combination of those fields are the primary key.

In order to import this data into my "Normalized" tables
in Access, I believe I need to first transfer the Injury
codes to a separate spreadsheet while still referencing
the claim number. I am assuming that there will need to
be multiple records per claim. Example:

record 1: [ClaimNumber1], [InjuryCode1]
record 2: [ClaimNumber1], [InjuryCode2]
record 3: [ClaimNumber1], [InjuryCode3]
record 4: [ClaimNumber1], [InjuryCode4]

Do I need VBA to do this? If yes, are there resources to
assist with this type of task since I have no experience
with adding VBA in Excel Please let me know if you need
additional information. I appreciate any help offered.

Thank you.
 
You might find it easier to import the complete sheet into
a temporary table in the database, then use a series of
queries to populate the live tables.
As well as guarding against changes to the spreadsheet
during your conversion, it would also provide a permanent
record of your source data.
If this need to be repeatable, it would pay to save the
queries and group them in a macro.
 
Back
Top