Reflexive table and the usual message : You can not add or change record because...

[MajP]

Was not suggesting the Access issue was related to the OS. Was suggesting the ability to post a .zip file to the site could be related to the browser or OS. My work computer cannot post an attachment due to security settings.

 

[gema57]

Was not suggesting the Access issue was related to the OS. Was suggesting the ability to post a .zip file to the site could be related to the browser or OS. My work computer cannot post an attachment due to security settings.

I need to post just another post, and I will be authorized to send an image. I will take a look where can I post, and I after I will be free

 

[MajP]

I will take a look where can I post, and I after I will be free

If you have an opinion on the US Govt Shutdown or Antarctic Ice Levels you can always go there.

 

[isladogs]

No need. Already at 10 posts
Did you try and attach a zip file earlier?

 

[gema57]

No need. Already at 10 posts

These are Database schemas in Microsoft Access, and the error when I try to set the first record. Hard each! :banghead:

 

[isladogs]

This isn't likely to solve your error but you have two joins to the same field in the Veterinary table. Remove one of them
You could do Stallion=>Follow=>Diagnostic=>Veterinarary instead

 

[gema57]

This isn't likely to solve your error but you have two joins to the same field in the Veterinary table. Remove one of them
You could do Stallion=>Follow=>Diagnostic=>Veterinarary instead

Indeed we could discuss further about the architecture of the database, and I agree with you, there could be enhancement relative to the relations and cardinality between the three tables Stalion, Diag, and Veterinary, however my most focus nowadays and immediatley is how to overcome the problem with the reflexive relation of Stallion Table. In the life, one animal must have just one father, and this animal could have 0 or several children. This is a natural law, and Iam sure in Microsoft Access the solution exists to comply to this law.

 

[isladogs]

It will take you less than 30 seconds to modify your relationships window as I suggested with no adverse side effects.

I didn't respond to the main issue as I thought MajP and others were on the case.
In fact if you review posts 2-7 & 10, you may already have a solution
If not and someone else doesn't come back to you first, I can probably look into it myself later

As for 'natural laws', some real life situations are remarkably complex to deal with in any computer system.
I doubt this is one of them but I haven't looked at it as yet.

If you do need more help, it would be useful to see your database with some realistic data included - it doesn't need to be real data

 

[gema57]

It will take you less than 30 seconds to modify your relationships window as I suggested with no adverse side effects.

I didn't respond to the main issue as I thought MajP was on the case.
If he doesn't come back to you first, I can probably look into it myself later

In fact It could not take 30 seconds as it is a part, an excerpt of the the Database, as I must refer to the team, discussion, political and endless debate . It must be seen later. Now our main problem is with this reflexive table Stallion inside Microsoft Access.

 

[MajP]

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.

 

[gema57]

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 :

 

[isladogs]

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'

 

[MajP]

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.

 

[gema57]

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

...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.

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

Old Europe

.
U are right, our problem with this stable is tricky, but maybe ...not unknown ...at least I hope

 

[MajP]

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.

 

[gema57]

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.

 

[Galaxiom]

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.

 

[MajP]

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.

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]

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.

 

[Galaxiom]

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.

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.