C
Confused24-7
Guest
How can I link a table to one specific item in another table in order to avoid repeating information? (ex: school name that would be narrowed down to its different departments)
How can I link a table to one specific item in another table in order to avoid repeating information? (ex: school name that would be narrowed down to its different departments)
- Local time
- Today, 06:24
- Joined
- Feb 28, 2001
- Messages
- 30,160
This question is SO wide open that I hesitate to try to answer it. If you don't know the answer to this question, you won't understand my answers, either. You need the vocabulary to even begin to understand how Access does this sort of thing. The good news is that Access does this perfectly well. The bad news is that if you don't understand how, you have a LOT of studying to do. The answer involves the following topics:
Primary and Foreign keys.
Many-to-one (or one-to-many) relationships.
Parent/child forms and tables.
Normalization.
You want to look up LOTS of help topics or find a book on "using Access" for beginners. If you are an experienced programmer but have not previously used a relational database, you MIGHT get by with an intermediate book. Otherwise, this is a true beginner's question and I don't know if I can answer it without confusing you. I would rather not do that.
Primary and Foreign keys.
Many-to-one (or one-to-many) relationships.
Parent/child forms and tables.
Normalization.
You want to look up LOTS of help topics or find a book on "using Access" for beginners. If you are an experienced programmer but have not previously used a relational database, you MIGHT get by with an intermediate book. Otherwise, this is a true beginner's question and I don't know if I can answer it without confusing you. I would rather not do that.
C
Confused24-7
Guest
Thank you for your reply.
I do have a beginner's book. I've read the portions on many-to-many, primary and foreign keys. However, I'm not sure of how to link two tables that share more than one item.
I'll keep researching..
Thanks,
Confused24-7
I do have a beginner's book. I've read the portions on many-to-many, primary and foreign keys. However, I'm not sure of how to link two tables that share more than one item.
I'll keep researching..
Thanks,
Confused24-7
I am fairly new to Access and I know how hard it is to get in to it. Understanding concepts is not always easy and once you understand them it all seems so obvious.
The first thing to think about is that the same data should never be repeated. If it is going to be written twice then it should be in its own table.
So lets say you have 100 pupils of which 50 go to one school and 50 to another.
So what you need is a table lets call it tblPupils - I find a naming convention helps me know when i am coding what I am doing e.g prefix with tbl - meaning table, frm meaning form etc. and on called tblSchools
The Pupils table records name date of birth etc.
A database doesn't care what data is in the database, but needs to have a way of knowing what data you are refering to. For this reason your first field should be PupilsID - again it is best to have an ID fields name which is diferrent to every other ID field in other tables. Set the field type as AutoNumber.
You now set this is the Primary Key, by right clicking on the edge of the table (Design Mode) and click the key symbol.
Repeat this Exercise for the schools table - tblSchools.
Now in this example the best way to refer to the schools table is by doing a lookup.
Create a field called School in the Pupils table. This will be the foreign key of the School Table.
A Foreign Key is the Primary Key from another Table i.e. what links them.
So now in the data field of School in the Pupils table choose lookup, follow the Wizard.
This will create a lookup so that you can choose which School each Pupil goes to.
Hope that helps with the basic concept. I would suggest that you read more around the subject.
Phil
The first thing to think about is that the same data should never be repeated. If it is going to be written twice then it should be in its own table.
So lets say you have 100 pupils of which 50 go to one school and 50 to another.
So what you need is a table lets call it tblPupils - I find a naming convention helps me know when i am coding what I am doing e.g prefix with tbl - meaning table, frm meaning form etc. and on called tblSchools
The Pupils table records name date of birth etc.
A database doesn't care what data is in the database, but needs to have a way of knowing what data you are refering to. For this reason your first field should be PupilsID - again it is best to have an ID fields name which is diferrent to every other ID field in other tables. Set the field type as AutoNumber.
You now set this is the Primary Key, by right clicking on the edge of the table (Design Mode) and click the key symbol.
Repeat this Exercise for the schools table - tblSchools.
Now in this example the best way to refer to the schools table is by doing a lookup.
Create a field called School in the Pupils table. This will be the foreign key of the School Table.
A Foreign Key is the Primary Key from another Table i.e. what links them.
So now in the data field of School in the Pupils table choose lookup, follow the Wizard.
This will create a lookup so that you can choose which School each Pupil goes to.
Hope that helps with the basic concept. I would suggest that you read more around the subject.
Phil
