Link to home
Start Free TrialLog in
Avatar of Richard Williams
Richard Williams

asked on

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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.
Avatar of Richard Williams
Richard Williams

ASKER

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

Thanks
<<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  https://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.
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
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.
Nice to hear!

Jim.
ASKER CERTIFIED SOLUTION
Avatar of Richard Williams
Richard Williams

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
He helped me find the missing ingredient to making my db work.    He was very helpful and I conveyed that to him