Access 2013 Table Relationships

I am using Access 2013 and thought I had learned how to link two tables together.  But I have discovered that the way I am linking them (& I have tried several various combinations) has not worked at all or is posting results to the wrong parent record.     For simplicity, I have Table 1:    ID, person, city, status;   Table 2:  ID, person, request, begin date, ending date, and result.      I am willing to attach a db with sample data if someone wants to see it
Richard WilliamsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This is what you should have:

Table 1:  tblPeople
PersonID
FirstName
LastName
City
Status

 Table 2:   Requests
RequestID,
PersonID
Request
Begin date
Ending date
Result.

  You would link these two tables on PersonID   In table 2, the PersonID is called a "Foreign Key", which is a copy of a key from another table, which serves as a pointer.

Jim.
Richard WilliamsAuthor Commented:
Ok Jim, is the PersonID found in both tables the PrimaryKey?  If so, what field type is the RequestID?  If not, what.  

Thanks
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Ok Jim, is the PersonID found in both tables the PrimaryKey?  If so, what field type is the RequestID?  If not, what.  >>

No.   I should have shown it a little better:

tblPeople
PersonID - Autonumber - PK
FirstName
LastName
City
Status

tblRequests
RequestID - Autonumber - PK
PersonID - Long - Foreign Key to tblPeople
Request
Begin date
Ending date
Result

 Actually neither is a "Primary Key" in a relational sense as they are autonumbers and meaningless, even though they are labeled as such (if you want to know a little more about that, read  http://www.experts-exchange.com/articles/2041/The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html  - It will be well worth your while and won't take long)

  But for the purpose of your question, let's say they are.

  When your designing a relational DB, each table represents one "thing".  In this case, we have people and requests.    The columns of a table are the attributes or facts about that one "thing".   Each row in the table needs to be uniquely identifiable in some way so we can tell them apart.

 Once you have the tables, then there may be relationships between them.     So you use a Foreign Key..   That's a copy of a primary key from another table.

 In this case, requests are related to people, so we have a field to store the PK from tblPeople in tblRequests.

 RequestID represents the request record itself.  PersonID tells us what record in tblPeople is related to this request.

So I might have:

tblPeople

1   Jim
2   Ron
3   John

Then requests:

RequestID   PersonID
1                     1
2                     1
3                     1
4                     3
5                     2
6                     2

So Jim has three requests, John has one, and Ron two.

Jim.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Richard WilliamsAuthor Commented:
I have edited this db and made the changes you suggested.  I am attaching this db & if you have a moment, check it.    If you do, in the form & query where I have included the Request field "PersonID" it displays a number.    I am assuming that I do not need to show this number in queries or reports?

By the way, this db only has sample data with two records.

Thanks
MasterList.accdb
Richard WilliamsAuthor Commented:
Jim, I got this database to work just fine.  I removed the sample data and put in 3 recipients and their request information.  Then I created a query & report and it looks just fine.   Thank you.  I also copied & pasted your link into my word processor so I read it again.  Thanks a lot.   I figured I was close and was missing a key step.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Nice to hear!

Jim.
Richard WilliamsAuthor Commented:
Appreciate your time and suggestion/advice.   Now I turn to my client db and see if what has worked in two db will work in it and then my check register one that I am working on for my son and myself.

Have a nice Thursday wherever you are this day.   I am in Oregon.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard WilliamsAuthor Commented:
He helped me find the missing ingredient to making my db work.    He was very helpful and I conveyed that to him
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.