UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Entry Problem With Linked Tables, Access 2016    
 
   
N.C.Barrett
post May 14 2019, 01:37 PM
Post#1



Posts: 11
Joined: 22-April 19



Since I am an Access beginner, apologies ahead of time if this is the wrong place for this question, the title is misleading, or both.

In Grover Park George's On Access, I am now learning how to create forms for regular data entry and modification. Instead of creating a personal contact database as the book instructs, I created a simpler database made of fictional families in which I have tried to carry out and preserve the logic the book asks for. (Fictional families would include the Jetsons, Flintstones, Seavers, Engles, etc.) I've honored the book's request to include enforcing referential integrity and cascade deletion of related records whenever possible.

Attached File  FakeDB.jpg ( 41.42K )Number of downloads: 8


Here's what the book instructed me to set up.

Attached File  BookDB.jpg ( 79K )Number of downloads: 10


I understand what lookup tables are for, and have added content to them as per their function.

Attached File  tl_RelationshipType.jpg ( 20.31K )Number of downloads: 0


Attached File  tl_ApproxAge.jpg ( 14.98K )Number of downloads: 0


To get the full benefit of the exercises in this chapter, I am instructed to start adding information to my database. I entered some info into t_Household to start, which Access accepted without complaint. I then tried entering information to create records in t_Person. When I try to create the first record, Access says "You cannot add or change a record because a related record is in table t_Household."

Attached File  DB_error.jpg ( 70K )Number of downloads: 4


I get this same error if I try putting data into t_PersonHousehold, but it complains about t_Person. I don't know how to solve this problem. Thanks for any help.
Go to the top of the page
 
theDBguy
post May 14 2019, 01:55 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,055
Joined: 19-June 07
From: SunnySandyEggo


Hi. The clue for me is the error message said "required," which means you tried to assign an ID number that does not exist in the lookup table. To avoid mistakes like this, you should use a form with a combobox to enter the looked up value.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post May 14 2019, 02:21 PM
Post#3


UA Admin
Posts: 35,516
Joined: 20-June 02
From: Newcastle, WA


Yes, this is a problem in the way I designed that, I think.

The problem is that the household table has a "HouseholdHeadID", which is related to the Person table using a one-to-one relationship. As theDBGuy says, the solution is going to be to change the required property of the HouseholdHeadID to "No", so that you can add the records to tblPerson and then, later, add them as HouseholdHeads. I actually think there's probably a better way to do this now than I did it then. I would move the HouseholdHeadID from tblHousehold to tblPersonHousehold in addition to making it not required.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
N.C.Barrett
post May 15 2019, 01:41 PM
Post#4



Posts: 11
Joined: 22-April 19



HouseholdHeadID already has "No" for the Required property.

If I move HouseholdHeadID to t_PersonHousehold, then I would get rid of t_Household and its other fields, correct?
Go to the top of the page
 
GroverParkGeorge
post May 16 2019, 09:38 AM
Post#5


UA Admin
Posts: 35,516
Joined: 20-June 02
From: Newcastle, WA


My apologies. I snapped off a response and left out a crucial bit of information.

What I do now is like this:

Attached File  HouseholdRoles.jpg ( 308.41K )Number of downloads: 4


I decided that each member of a household--not just the household head--should have a "role" in that household.

So, now I include the HouseholdRoleID in the table that links individuals to the household(s) in which they reside.

That allows me to designate one person as the Household Head. That could be either partner in a marriage or in a non-marriage situation, or a single person. Then, I can designate a spouse/significant other and also other members. That would usually be the children, but could be a non-related person sharing the household. This gives more flexibility and, I think, better normalization.

Again, sorry for being too quick off the mark with an incomplete response.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
N.C.Barrett
post May 17 2019, 08:24 PM
Post#6



Posts: 11
Joined: 22-April 19



Gonna go on a tangent to clarify. Since I am learning how to setup and maintain databases, I didn't know you left out information. My only aim was to ensure I follow your logic properly, especially since in this case I take my own path to understanding.

Partly for simplicity and partly because I'm using (older) fictional characters, I'm going to stay with what I set up. "fkHouseholdID" and "fkPersonID" in t_PersonHousehold parallel "IndividualHouseholdID" and "IndividualID," respectively, in tblIndividualHousehold. If I understand your point correctly, it sounds like I should move HouseholdName to t_PersonHousehold and eliminate the rest of t_Household and its links. It would be a Short Text field with no need for a new link between tables.

Attached File  FakeDB.jpg ( 41.42K )Number of downloads: 0


to

Attached File  Updated_FakeDB.jpg ( 36.72K )Number of downloads: 0


Aside from redundancy between fkHouseholdID and HouseholdName, that radically changes the state of this database. Wouldn't HouseholdName need to be derived from the husband's last name? (I know that a query would help automatically sort out that information, but I'm getting ahead of myself for now. laugh.gif )

Another couple tangents here:

I need to ask a weird question. In your database, I see 2 tables that link off tblIndividualHousehold, tlkpHouseholdRole and tblHousehold. The first is, perhaps obviously, a lookup table. The second isn't; it has three fields and a field called InActiveDate that I see repeated in tblIndividualHousehold and tblIndividual. Do those relationships make that field's value appear in those tables?

This exercise does instruct me to set up a combobox as the DBguy suggests, likely meaning I went as far as I was supposed to with data entry.
Go to the top of the page
 
GroverParkGeorge
post May 18 2019, 07:44 AM
Post#7


UA Admin
Posts: 35,516
Joined: 20-June 02
From: Newcastle, WA


Hi, I appreciate the opportunity to revisit an old friend, but you are sort of exposing some things that could have been done better.

Anyway, a couple of responses.

" Wouldn't HouseholdName need to be derived from the husband's last name" Up to you. I consider that to fall more into the Business Rules category. Traditionally, of course, that's how it is done in most places around the world with which I'm familiar, but it is, in the end, a decision you make about the way you want to model your data.

Households are, in my view, a group of people who reside at a single location. That can be a single person, a married couple, an unmarried couple, a couple with one or more children, an unmarried couple with one or more children, a grandparent, parent and child, or whatever other combination you can think of. I was forced to reckon with this when I set out to build an attendance database for my daughter's school many years ago. It quickly became apparent that the traditional "married couple with kids" approach wasn't adequate. So, that's why you need to identify "Household" as an entity without specifying much more detail than that. In order to capture the MEMBERS of that household, you need to create the junction table to join each household to one or more individuals. And, as I also learned, some individuals "live" in two or more households. Think divorce and shared custody. Even "mom in jail, dad in an apartment, kids living with grandma for the time being." You MUST allow for a many-to-many relationship between individuals and the "households" to which they belong. And, as I previously mentioned, I came to realize that "Household Head" was itself a domain, not a binary attribute.

"Household Head"-- the individual who speaks on behalf of the household. Maybe a husband in a traditional marriage, sure. But same-[g e n d e r] partners? One is the head of the household? Maybe, maybe not. Widowed Grandma raising the grandkids? Uh, no. not husband, but definitely "Head of Household."

Can you simplify all of that and create a design that doesn't permit a person to belong to more than one household? Sure, but if that doesn't fit the real world you need to model for, it's going to break. I was forced to put my model for the book to the test in the real world and found it came up short.

HTH

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
N.C.Barrett
post May 23 2019, 01:46 PM
Post#8



Posts: 11
Joined: 22-April 19



I've changed course and built a personal contacts database as the book asks for. I share the following to make sure I followed what you said.

Attached File  PersonalContactsDB.jpg ( 68.45K )Number of downloads: 3
Go to the top of the page
 
mike60smart
post May 23 2019, 03:36 PM
Post#9


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi

Only field I would add is PhoneTypeID to t_Phone together with a tbluPhoneTypes (This would contain the list of Phone Types - Home, Work, Mobile)



--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
N.C.Barrett
post May 25 2019, 01:48 PM
Post#10



Posts: 11
Joined: 22-April 19



I just added that. Thanks.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 06:56 AM