Help with relationships
- Thread starter claireB
- Start date
Hi. I'v enforced RI. My problem is i'm a beginner, and i'm not sure what i'm doing wrong. I cant seem to get the forms right, evertime i try to input information i get error messages saying a particular field is not included in the recordset, hence why i believed my relationships to be wrong. Any suggestions?
- Local time
- Today, 13:46
- Joined
- Feb 28, 2001
- Messages
- 30,557
Your problem might be visualization, since you claim to be a beginner. That ain't a fatal disease. Working with Access, you will eventually learn how to spot such issues as you have just described.
A relational database supports a couple of different kinds of relationships. The rare case is a one-to-one, used only for supplemental information in very complex and hard-to-describe situations. The more common situation is many-to-one (or one-to-many when turned upside down).
The many-to-one case is usually a lookup. Like, say, USA two-character state codes when you have lots of info that is state-related. The state-code becomes the lookup key for the rest of the state data.
Turn that around to a one-to-many and you have a parent/child relationship in which one record is associated with many subsidiary records. For instance, an invoice entry with many line items. An employee time sheet header with many time sheet individual charges. A department table with many assigned employees per department. A part number with many add/draw stock transactions.
To visualize this, you have to think about orphans. When you are enforcing RI, you cannot have orphans. If there is a stock transaction, it has to correspond to a part number - which means there must be a table holding that part number. To have a timesheet charge, you must have it for an employee. There can be no orphans.
For the lookup case, there can be no unknown lookups. That is, if your state code is TV (as oppose to, say, TN or TX), you can't look up the state information. RI would also prevent this case.
So when you are having trouble with entering data, the issue is USUALLY that you are trying to enter things in the wrong order - creating a child without first creating the parent, or creating a reference without first defining a referenceable item.
Remember, the relationships are usually DIRECTIONAL. If you define records in the direction opposite the relationship definition, you get error messages. Make more sense that way?
A relational database supports a couple of different kinds of relationships. The rare case is a one-to-one, used only for supplemental information in very complex and hard-to-describe situations. The more common situation is many-to-one (or one-to-many when turned upside down).
The many-to-one case is usually a lookup. Like, say, USA two-character state codes when you have lots of info that is state-related. The state-code becomes the lookup key for the rest of the state data.
Turn that around to a one-to-many and you have a parent/child relationship in which one record is associated with many subsidiary records. For instance, an invoice entry with many line items. An employee time sheet header with many time sheet individual charges. A department table with many assigned employees per department. A part number with many add/draw stock transactions.
To visualize this, you have to think about orphans. When you are enforcing RI, you cannot have orphans. If there is a stock transaction, it has to correspond to a part number - which means there must be a table holding that part number. To have a timesheet charge, you must have it for an employee. There can be no orphans.
For the lookup case, there can be no unknown lookups. That is, if your state code is TV (as oppose to, say, TN or TX), you can't look up the state information. RI would also prevent this case.
So when you are having trouble with entering data, the issue is USUALLY that you are trying to enter things in the wrong order - creating a child without first creating the parent, or creating a reference without first defining a referenceable item.
Remember, the relationships are usually DIRECTIONAL. If you define records in the direction opposite the relationship definition, you get error messages. Make more sense that way?
leemorris2005
New member
- Local time
- Today, 19:46
- Joined
- Jul 24, 2005
- Messages
- 6
Hi Claire
I think what The_Doc_Man is saying is that you will get errors trying to input data into linked tables if you do not do it in the right order.
i.e. if you have a table full of car makes, and a table full of car models:
if the car make table contains aston martin and ferrari, you can only have cars in the model table that belong to either aston martin or ferrari, if you try to put a ford in there, it will be an orphan because there is no corresponding ford record in the make table. so to get around this, you would add ford first to the make table, and then the car model.
Hope this helps.
I think what The_Doc_Man is saying is that you will get errors trying to input data into linked tables if you do not do it in the right order.
i.e. if you have a table full of car makes, and a table full of car models:
if the car make table contains aston martin and ferrari, you can only have cars in the model table that belong to either aston martin or ferrari, if you try to put a ford in there, it will be an orphan because there is no corresponding ford record in the make table. so to get around this, you would add ford first to the make table, and then the car model.
Hope this helps.
