Problems with relationships between tables. (1 Viewer)

dunksrn

New member
Local time
Today, 08:12
Joined
Aug 23, 2017
Messages
2
Firstly, I apologize if this question has been answered elsewhere, I wasn't sure about the right wording to find the answer. Here's my issue:

I got burned with the current database I am building because I used lookups at the table level. I have since fixed them and removed all the lookups. I have two tables: tblFamily and tblChild. In the child form I have the ChildLastName combobox which looks up the FamilyLastName values from the tblFamily table; and stores the value in the ChildLastName field (it is a short text field type). The problem is that it seems as though that value is stored alone and if, for instance, I update the family last name it does not update the ChildLastName value.
The combobox is set up as follows:
Column count: 1
Bound column: 1
Data: ChildLastName
Lookup: FamilyLastName from tblFamily

I'm sorry if this is a very beginner question, thank you for any help!
 

plog

Banishment Pending
Local time
Today, 07:12
Joined
May 11, 2011
Messages
11,613
What you are trying to do is store data in multiple places, that's not how databases are to work. Instead, they use keys.

A table has a primary key which makes each record unique. That primary key is then used in another table as a foreign key thus relating the records. You wouldn't store the same data in each table, you would rely on that relationship to obtain the updated data.

For your scenario you should have this:

tblFamily
FamilyID, FamilyLastName, ...,
1, Smith
2, Jones


tblChild
ChildID, FamilyID, ChildFirstName, ...
1, 1, John
2, 1, Sally
3, 1, Steve
4, 2, Tim

So when you want a child's last name you relate those two tables by the FamilyID field and get the FamilyLastName field. That also means when you change FamilyID=1 to FamilyLastName=Harris the 3 records in tblChild related to it automatically have thier last name updated as well--without change anything other than one field in tblFamily.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:12
Joined
Feb 19, 2002
Messages
42,981
Most children have the surname of their father. However, there are situations where that is not the case. Blended families are one example. Depending on the situation, the "father" may not be able to adopt the children of the mother or the children may be old enough to not want to change their names. Therefore, LastName even though it may seem redundant is an attribute of the child record. Given that, you would need to update the child records yourself. You can do it with an update query. I would use criteria in the query that in addition to selecting the familyID also checks the surname and only changes the surname for the children with the prior name. So if the surname is Smith and three of the children are Smith's but one is Clark, then if the parent name changes from Smith to Jones only the Smith's should change to Jones and the Clark should remain as it was.
 

Users who are viewing this thread

Top Bottom