Need to create primary key for records with no unique value

[6two6rob]

Okay, so I work at a law firm and we have two main tables of records put together by different departments (excel spreadsheets that I've imported into access).



So the first sheet is made by my department, and we put in client case numbers along with last four of social security so each value in this field is unique.

The problem is we have a second table which shows the same clients with additional information which I do not have. These records contain client names and emails etc, but the problem is there is no one field in the spreadsheet that has a unique record.

I was wondering if there is some way to create a relationship between these two tables so I can look up a person by name on my table (which has a unique primary key) and find the person's records on another table.

Thanks, I hope to contribute to this site as well.
-Roberto

My attempt at an example:

[orange]

First, get a good description of what you are trying to do. Keep it in simple, plain English.
Once you get a description of your Business issue/opportunity, bounce it off your colleagues to make sure its complete. Do NOT try to embed Access/Database/jargon into the description.

Also, you really should do a tutorial on Database design. Tables and relationships are fundamental concepts in database. A table deals with a specific subject. A primary key is a field whose value is unique and serves to distinguish every record in a table from every other record in that table.

People names are PoOr candidates for a Primary key.

I'm not sure of the scope of your activity, but you might get some ideas from this free data model of Legal Case management and the associated business rules.

Good luck.

[June7]

Names make very poor unique identifiers. You can build a query that has compound link on the common name fields but since you already identified the issue of people with same name, this will not produce valid output. The answer is that without a PK/FK relationship there is no way to link these tables - GIGO syndrome - Garbage In Garbage Out.

[6two6rob]

Thank you orange. I will look into this and return here when I have a better view of what I need to get done.

I am new to Access, but excited to learn more about Database design!

@June7:

Very cool, now I have some material I can research to better understand Access. Thanks!