Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-30-2019, 06:32 AM   #31
gema57
Newly Registered User
 
Join Date: Jan 2019
Posts: 17
Thanks: 28
Thanked 0 Times in 0 Posts
gema57 is on a distinguished road
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post
By the naming convention, I think there may be a logic issue making this more difficult to think about. Your field in the table is called IDStallion_IsFatherID. I am not saying you are doing this, but that leads me to believe you are pointing to a child not that records parent. My assumption may be wrong but hopefully that value points to the current records parent.

If that is the case as I said there are three options
1) Do not require the IsFatherID and leave it blank for a unknown Parent. This represents a top level
2) Require the isFatherID and if it is unknown the FK and PK would be the same.
3) Require the isFatherID and enter a Dummy stallion. For the Dummy record/s set the PK and FK to be the same. Reference stallions with unknown parents (not in database) to the dummy or dummies

I do not see the need for 3, but there may be utility.
I think I understood you. It is more useful if I show you the conceptual schemas of this Database, as the previous collegue, you are pointing toward the relationships of this table. U are asking me, if you could not find a stallion's father in your stable, then what do you do ? Correct, and relevant question. Because of course there is an issue about stallions that don't have father known in this stable, however these stallions have all a father, but an unknown father, that we could not know. An unknown father, that are not present in the stable, means the stallions have or maybe not a father in this stable. In this case the maybe changes all. Did I understand you correctly ?

Below here is my conceptual schemas about the database :

gema57 is offline   Reply With Quote
Old 01-30-2019, 06:52 AM   #32
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,081
Thanks: 105
Thanked 2,445 Times in 2,247 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Reflexive table and the usual message : You can not add or change record because.

Apologies for the digression but the last post reminds me of the famous quote by Donald Rumsfeld (US Secretary of State under George Dubya Bush):

"As we know, there are known knowns; there are things we know we know.
We also know there are known unknowns; that is to say we know there are some things we do not know.
But there are also unknown unknowns the ones we don't know we don't know.
And ... it is the latter category that tend to be the difficult ones."


Substitute fathers for 'knowns'
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
gema57 (01-30-2019)
Old 01-30-2019, 08:59 AM   #33
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
An unknown father, that are not present in the stable, means the stallions have or maybe not a father in this stable
Not sure if I understand. I would assume if the horse has a father that is going to be entered into the DB, that you enter the father first. In other words you need to load the oldest generations first. If you do not want to do it in that order and just enter them randomly, do not make the parent key required until you have done all entries. Then verify all of your entries and update the parent key. Then either assign the top level to itself or put in a dummy parent. At this point every record should have a parent ID either pointing to each real parent or itself (or the dummy record if you choose that approach). Now make the parent key required for any future entries. Now you have referential integrity and can enforce they assign a real parent or identify themselves as the top of the lineage.

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
gema57 (01-30-2019)
Old 01-30-2019, 12:04 PM   #34
gema57
Newly Registered User
 
Join Date: Jan 2019
Posts: 17
Thanks: 28
Thanked 0 Times in 0 Posts
gema57 is on a distinguished road
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post
Not sure if I understand. .... themselves as the top of the lineage.
Iam realizing the problem is more harder than I could imagine. Tomorrow I will have more undoubtedly more questions. It seems
Quote:
...you need to load the oldest generations first. ... just enter them randomly, do not make the parent key required until you have done all entries.
It seems to be somehow tricky, however the reality is far to be simple, and most of the time it is more than tricky.

Quote:
Apologies for the digression but the last post reminds me of the famous quote by Donald Rumsfeld...

In Europe and specifically in France -where iam -, Belgium, and Germany D. Rumsfeld is well known more than any US politician, as the author of
Quote:
Old Europe
.
U are right, our problem with this stable is tricky, but maybe ...not unknown ...at least I hope
gema57 is offline   Reply With Quote
Old 01-30-2019, 12:35 PM   #35
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Reflexive table and the usual message : You can not add or change record because.

Lets say I have
Horse A (Father not ever part of the stable so not in database)
Horse B (father is A)
Horse C (Father is B)

The lineage or Pedigree is

Horse A (Grand Parent)
-- Horse B (parent)
---- Horse C

If you can enter records in order from oldest generation to youngest A, B, C then you can enforce the Parent ID at the start.

I would enter it like

ID Horse ParentID
1 HorseA 1
2 HorseB 1
2 HorseC 2

If you want to enter not in order then you cannot require the parent key in the beginning until you get your data in. Then you can enforce it.
You cannot enter Horse C to start because you have not entered HorseB. You cannot enter HorseB because you have not entered HorseA.

You could enforce it to start, add all your horses and self reference the ones whose parent is not in the db yet. Then go back and correct the parents. That seems likely to cause a mistake, but would work.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 12:00 PM   #36
gema57
Newly Registered User
 
Join Date: Jan 2019
Posts: 17
Thanks: 28
Thanked 0 Times in 0 Posts
gema57 is on a distinguished road
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post

If you can enter records in order from oldest generation to youngest A, B, C then you can enforce the Parent ID at the start.

I would enter it like

ID Horse ParentID
1 HorseA 1

.... but would work.
This is the first reflex, ideas of the colleagues, of us, however nothing work. Maybe we were wrong. The first record could not be register. Tomorrow, if you want I can post the Database entirely because now I can post with attached files.
gema57 is offline   Reply With Quote
Old 01-31-2019, 01:30 PM   #37
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,459
Thanks: 85
Thanked 1,448 Times in 1,367 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post
IMO there is No reason to add a dummy parent at all. Does not really give you anything. Lets say you add dummy record with an ID of 1. In order to enforce referential integrity and also require a parent ID, the dummy record Parent ID FK has to relate to something. So normally you relate the top level to themselves and this record gets a Parent FK of 1.
So in my opinion you have created an artificial top layer. Instead if Horse 7 has an unknown parent just make its Parent ID FK 7.
So the horse would be designated as its own parent. I would be more inclined to have a rule that prevents this possibility.

(I know artificial breeding has come a long way but I don't think it has gone that far yet.)

Moreover, the whole point of the dummy record to indicate unknown is to support referential integrity which precludes a record referring to itself.

Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 03:14 PM   #38
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
So the horse would be designated as its own parent. I would be more inclined to have a rule that prevents this possibility.
So as I pointed out this gains you nothing, but if it makes you feel better then it hurts nothing. If you put in a dummy record it will have to have a parent id. So what is its parent ID? You have to self reference. So what is the benefit and difference of having a dummy record to identify the top level when you can identify the real top level by self referencing. Maybe in a SQL recursive query there is a need, but in Access any recursion is done in code.

So lets assume instead of a stable this is a systems of systems database that relates with systems, subsystems... subsubsystems...
You enter 1000 top level items would you enter 1000 unique dummy records? Would you artificially relate these 1000 systems to a single dummy that also has no meaning. Not saying this will not work, but it is as artificial as self referencing to ID the top level.

Quote:
Moreover, the whole point of the dummy record to indicate unknown is to support referential integrity which precludes a record referring to itself
That is not true in Access as I pointed out. This may be the case in other DBs, I do not know. Give it a try. You most certainly can self reference and enforce referential integrity.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 03:16 PM   #39
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Tomorrow, if you want I can post the Database entirely because now I can post with attached files.
Please post. My guess it is something simple you are overlooking, and may be related to other relationships. Please strip it down to pertinent tables only. Do not need all forms, reports and code.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 06:33 PM   #40
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,459
Thanks: 85
Thanked 1,448 Times in 1,367 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post
Quote:
Originally Posted by Galaxiom View Post
Moreover, the whole point of the dummy record to indicate unknown is to support referential integrity which precludes a record referring to itself.
That is not true in Access as I pointed out. This may be the case in other DBs, I do not know. Give it a try. You most certainly can self reference and enforce referential integrity.
You are correct. I had never tried it before. I was going on PatHartman's explanation earlier in the thread.

Quote:
Originally Posted by Pat Hartman View Post
If you make the parent field required, it CANNOT be null. Never under any conditions would a non-null value be valid if a related record does not exist. Therefore record #0 must ALREADY exist in order to be acceptable as a parent.

This is unreconcilable if you have RI enforced AND set the parent field as required. You cannot add the dummy record if RI is enforced AND the parent field is required. (Emphasis added)

You can remove all RI and the requirement that the parent field be not null. You can then add a dummy record. Once the dummy record is added, you can modify the parent field and fill in the dummy record ID. Then and only then can you reestablish RI and enforce the required rule for the parent field.
Apparently Pat is mistaken.

I would still use a separate record to represent an unknown parent. This makes the selection of the unknown parent explicit rather than potentially an entry error.

I would also add a record validation that the ParentID cannot be the same as the ID. Obviously this would need to be added after the unknown parent self referenced entry was inserted.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 08:23 PM   #41
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
I would still use a separate record to represent an unknown parent. This makes the selection of the unknown parent explicit rather than potentially an entry error.

I would also add a record validation that the ParentID cannot be the same as the ID. Obviously this would need to be added after the unknown parent self referenced entry was inserted
If it makes you feel good go for it. I have never had the need but I do not see it hurt anything. I would just have to change how the recursive calls kick out. I have done a ton of large recursive databases (organizations, personnel and equipment, data models, engineering components) with 10s of thousands of nodes going hundreds of levels deep. To me this discussion is somewhat academic anyways, because in the applications I build this would never be an issue because the user would never manually input an FK. Normally if it is reflexive I am using a Treeview, and have no concern about the user worrying about FKs. If they drop a node on the main branch it gets a self referncing key. If they enter or drag it to an existing node then they get the PK from the parent node. This will always be transparent to the user. To create a node you either find a parent click on it and get the pop up to enter a child. To create a top level node you click on the top level. If somehow a mistake was made and it was not supposed to be a top level node, it will be clearly visible and you would just drag it to where it needs to go or select a parent from a list. However, what is very important is referential integrity. Having an orphan in a normal table is not a huge deal, in a recursive build it can be huge. Since you are normally doing recursive loading of the tree, one bad FK could cause thousands of relations to be lost in the recursion (break a branch).

For example the Treeview demo I did for Inflights bird breeding program that could show all offspring or all parents.

Normally I am using something like this to enter records.
Attached Images
File Type: jpg Treeview.jpg (54.2 KB, 53 views)
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
gema57 (02-11-2019)
Old 01-31-2019, 08:54 PM   #42
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,459
Thanks: 85
Thanked 1,448 Times in 1,367 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Reflexive table and the usual message : You can not add or change record because.

Quote:
Originally Posted by MajP View Post
For example the Treeview demo I did for Inflights bird breeding program that could show all offspring or all parents.
A tree view seems the perfect way to display birds.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
gema57 (02-11-2019)
Old 02-01-2019, 12:36 AM   #43
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,081
Thanks: 105
Thanked 2,445 Times in 2,247 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Reflexive table and the usual message : You can not add or change record because.

Ho ho

Sorry for the digression or for branching off from the main discussion again.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
gema57 (02-11-2019)
Old 02-11-2019, 01:07 PM   #44
gema57
Newly Registered User
 
Join Date: Jan 2019
Posts: 17
Thanks: 28
Thanked 0 Times in 0 Posts
gema57 is on a distinguished road
Re: Reflexive table and the usual message : You can not add or change record because.

Finaly we decided to change the cardinalities. It means a stallion could have or not a father, in fact regarding the context it is logical. Hoiwever this exercise and question could be useful for others that met, meet, or will met this problem in Microsoft Access. It is useful to keep this post in your database.
I want to say thx very much to you all for your help, finally U did help us to see the reality of the problem.
Regards.

gema57 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: ou cannot add or change a record because a related record is required in table Zak14 General 10 08-14-2014 10:55 PM
query for reflexive relation sonu.22avril Queries 5 12-25-2010 03:23 PM
"You cannot add or change a record because a related record is required in table.... sclarkin Forms 10 08-16-2006 06:44 PM
add or change record error message jduke44 Forms 2 05-31-2006 01:13 PM
Reflexive relation Fluffy Tables 9 09-24-2003 11:00 AM




All times are GMT -8. The time now is 09:14 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World