Adding a record where Primary Keys are not known

[Ilan Sebba]

I am trying to add a record using SQL. My problem is that the primary keys
are foreign keys, and these foreign keys are autonumbers. I therefore do
not know the primary keys of the record I am trying to insert. I therefore
do not think that I can use the sql "Insert Into" command.

Here is a simplified illustration of my tables:

tblFather
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]

tblMother
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]
tblChild
FatherFK [PK1]
MotherFK [PK2]
ChildFields

Now say that mother and father give birth. I know the natural keys of both
parents. I should think that I have to do some sort of Join, but I am not
sure how to proceed.

Many thanks
Ilan

[Peter Russell]

Ilan Sebba previously wrote:

Now say that mother and father give birth. I should think that I
have to do some sort of Join, but I am not
sure how to proceed.

(I seem to recall the same sort of problem.. but that was many years ago!)

In principle you have to lookup the ID of each parent using the keys that
you know. Then you can create your new record.

It would look something like this but it will depend on how you are
getting your natural key values and whether they are number or text.

Dlookup("[ID]","tblFathe r","[Naturalkey1]= knownvalue1 AND [NaturalKey2] =
knownvalue2")

Regards

Peter Russell

[Chuck Van Den Corput]

On Tue, 11 Nov 2003 13:03:31 GMT, "Ilan Sebba" <ilan underscore sebba
at btinternet stop com de***@yahoo.com> wrote:

I am trying to add a record using SQL. My problem is that the primary keys
are foreign keys, and these foreign keys are autonumbers. I therefore do
not know the primary keys of the record I am trying to insert. I therefore
do not think that I can use the sql "Insert Into" command.

Here is a simplified illustration of my tables:

tblFather
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]

tblMother
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]
tblChild
FatherFK [PK1]
MotherFK [PK2]
ChildFields

Now say that mother and father give birth. I know the natural keys of both
parents. I should think that I have to do some sort of Join, but I am not
sure how to proceed.

Many thanks
Ilan

If I understand the situation, I don't think you are missing anything
to be able to proceed. Give the tblChild record it's own, unique
primary key (using an AutoID). Then, as you have done, ensure that
there is one field to store the key for the father and another for the
key for the mother.

Given a father, you can then determine all his offspring. Given a
mother, you can do the same. Given a child, you can identify his
parents.

Chuck

[Ilan Sebba]

Thanks for your reply.

I understand your solution, you are saying: retrieve the IDs of the records
in the parent tables and, armed with these keys, proceed to insert a record
into the child table.

I accept that this will work. However, I feel that there should be a more
elegant way, one which takes into account the existence of relationship
which exists between the parent tables and the children tables. It could be
that I am expecting too much from the relational database.

Anyway, the solution you propose works, and I will use it for now.

Many thanks
Ilan
"Peter Russell" <ru***@127.0.0. 1> wrote in message
news:me******** **************@ russellscott.bt internet.com...

Ilan Sebba previously wrote:

Now say that mother and father give birth. I should think that I
have to do some sort of Join, but I am not
sure how to proceed.

(I seem to recall the same sort of problem.. but that was many years ago!)

In principle you have to lookup the ID of each parent using the keys that
you know. Then you can create your new record.

It would look something like this but it will depend on how you are
getting your natural key values and whether they are number or text.

Dlookup("[ID]","tblFathe r","[Naturalkey1]= knownvalue1 AND [NaturalKey2] =
knownvalue2")

Regards

Peter Russell

[Ilan Sebba]

Hmmm. I don't think I clearly explained my problem. Yes, the child tables
can be as follows:

tblFather
NaturalPK1
NaturalPK2
OtherFields
ID

tblMother
NaturalPK1
NaturalPK2
OtherFields
ID

tblChild
FatherFK [PK1]
MotherFK [PK2]
DateOfBirth [PK3]
OtherFields
ID (optional?)

My problem is this. I have a mother (i know her primary keys, but not her
autonumber ID) and I have a father (I know his primary keys, but not his
autonumber ID). I now want to say 'these two parents, they are going to
have a baby today - I want to insert the record'. I want to insert the
record using SQL. I know that MS-Access forms can do this very easily -
without the end user having to worry about IDs.

The solution posted by Peter Russell(if I understood correctly) is to use
the parents primary keys to look up their IDs, and then to insert that
record for the child. That works, but I feel that this fails to use the
power of the relational database, because it does not make use of the
relationship which exists between the parent tables and the child tables.
But would there be a method where I can join the three tables together and
then add a new record to this expanded table, without having to retrieve the
parent IDs?

Many thanks

Ilan
"Chuck Van Den Corput" <NO************ *****@sympatico .ca> wrote in message
news:9o******** *************** *********@4ax.c om...

On Tue, 11 Nov 2003 13:03:31 GMT, "Ilan Sebba" <ilan underscore sebba
at btinternet stop com de***@yahoo.com> wrote:

I am trying to add a record using SQL. My problem is that the primary keysare foreign keys, and these foreign keys are autonumbers. I therefore do
not know the primary keys of the record I am trying to insert. I thereforedo not think that I can use the sql "Insert Into" command.

Here is a simplified illustration of my tables:

tblFather
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]

tblMother
NaturalKey1 [PK1]
NatuarlKey2 [PK2]
SundryFields
ID [Autonumber]
tblChild
FatherFK [PK1]
MotherFK [PK2]
ChildFields

Now say that mother and father give birth. I know the natural keys of bothparents. I should think that I have to do some sort of Join, but I am notsure how to proceed.

Many thanks
Ilan

If I understand the situation, I don't think you are missing anything
to be able to proceed. Give the tblChild record it's own, unique
primary key (using an AutoID). Then, as you have done, ensure that
there is one field to store the key for the father and another for the
key for the mother.

Given a father, you can then determine all his offspring. Given a
mother, you can do the same. Given a child, you can identify his
parents.

Chuck

[Peter Russell]

Ilan Sebba previously wrote:

Thanks for your reply.

I understand your solution, you are saying: retrieve the IDs of the
records
in the parent tables and, armed with these keys, proceed to insert a
record
into the child table.

I accept that this will work. However, I feel that there should be a
more
elegant way, one which takes into account the existence of relationship
which exists between the parent tables and the children tables. It
could be
that I am expecting too much from the relational database.

Until the child record exists there is nothing for the mother or father
records to link to.

The situation is different with forms where Access automatically fills in
the value of a the link field in the child table.

Regards

Peter Russell

[Ilan Sebba]

"there is nothing for the mother or father records to link to"

That makes sense - I never really thought about what the link actually
links.

Many thanks

Ilan Sebba

"Peter Russell" <ru***@127.0.0. 1> wrote in message
news:me******** **************@ russellscott.bt internet.com...

Ilan Sebba previously wrote:

Thanks for your reply.

I understand your solution, you are saying: retrieve the IDs of the
records
in the parent tables and, armed with these keys, proceed to insert a
record
into the child table.

I accept that this will work. However, I feel that there should be a
more
elegant way, one which takes into account the existence of relationship
which exists between the parent tables and the children tables. It
could be
that I am expecting too much from the relational database.

Until the child record exists there is nothing for the mother or father
records to link to.

The situation is different with forms where Access automatically fills in
the value of a the link field in the child table.

Regards

Peter Russell

[Pavel Romashkin]

I suppose the "elegant" solution would be not to use the autonumbers. In
this case (unless, of course, the parent tables already have records you
are referencing in the child table), you can create the parent tables'
PKs and insert them into parent tables, then into the junction table.
This is what I have to do when interfacing Access BE with Palm-based FE.
I used the autonumbers but the exact problem you describe forced me to
use buffer tables and SQL pre-processing to put PAlm DB data into the
right tables in the right order - parents first, children last, to avoid
ref. integrity violations.

Pavel

Ilan Sebba wrote:

Thanks for your reply.

I understand your solution, you are saying: retrieve the IDs of the records
in the parent tables and, armed with these keys, proceed to insert a record
into the child table.

I accept that this will work. However, I feel that there should be a more
elegant way, one which takes into account the existence of relationship
which exists between the parent tables and the children tables. It could be
that I am expecting too much from the relational database.

Anyway, the solution you propose works, and I will use it for now.

Many thanks

Ilan

"Peter Russell" <ru***@127.0.0. 1> wrote in message
news:me******** **************@ russellscott.bt internet.com...

Ilan Sebba previously wrote:

Now say that mother and father give birth. I should think that I
have to do some sort of Join, but I am not
sure how to proceed.

(I seem to recall the same sort of problem.. but that was many years ago!)

In principle you have to lookup the ID of each parent using the keys that
you know. Then you can create your new record.

It would look something like this but it will depend on how you are
getting your natural key values and whether they are number or text.

Dlookup("[ID]","tblFathe r","[Naturalkey1]= knownvalue1 AND [NaturalKey2] =
knownvalue2")

Regards

Peter Russell