How do I relate these access tables without losing data?

Question

I have an excel flat file with about ~6k records I want to put into a MS Access database. The original Excel fields are:

Name
Address
City
State
Zip
DOB
CaseNumber
DateSentenced
AmountDue

I saved everything in the Excel file into smaller text files by column like:

NAMES
id
last
first
middle
suffix

ADDR
id
line1
city
state
zip

BDAYS
id
dob

CASENO
id
casenumber
courtnumber

SENTENCED
id
datesentenced

AMOUNTS
id
amountdue

TOAGENCY
id
year

I imported each of these into an Access table and made sure they had no errors.

So far I have relationships set up like:

Each one has Enforce Referential Integrity & Cascade Update checked.

My problem is that the flat file had many duplicate names, addresses, dobs for a different case number; since one person can have many cases.

How do I fix this without making the universe implode? I'm extremely new to this kind of thing & don't know what question to ask :(.

Answer 1

The short answer is that there is no silver bullet. Nothing will clean up the data for you. You either have to do it manually, or you have write program that normalizes the data by some set of rules that you define.

Answer 2

There are a number of things wrong with your data design.

How many birthdays can one person have? One. So the birthday belongs in the Names table as a field. I believe you are saying a person can have more than one address. That's ok, and you have one name related to many possible addresses - that's fine. But you can't just connect the two tables by their primary ID field. That would mean name 1 connects to address 1 - which may or may not be true. What if Name 1 connects to addresses 5 and 7? You must have a Name ID field in the Address table. Then Addresses ID 5 and ID 7 can have Name ID 1 in the Address table making the connection you want. That's called a foreign key.

Then you have three tables, Amounts, ToAgency, Sentence, all connected to the JP_Case table as one-to-one relationships. That is to say there is only one Amount, ToAgency, and Sentence for each Case. Just like the Name-Birthday, if the Case can only have one of each of those three values, they should be fields in the JP_Case table, not separate tables.

Here's a tutorial on relational data design that should put you on the right track.