How do I add an autonumber

[Ilan Sebba]

I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give birth to identical mules.
So each android and a martian have primary key which is a foreign key to the
parent table. Now, I want to insert a new record in 'android' or 'martian'.
This can be done easily using MS-Access forms. But I want to do so using
SQL. To add a martian, I first need the parent ID. How do I know what it
is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent.
How do I do that, if I don't know the autonumber value of the record?

Many thanks for your help
Ilan

[Ilan Sebba]

Apologies for the early post before proof reading. Here is my post again,
proof read:

I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Androids and Martians have only one thing in common: they can unilaterally
birth to mules.
Each android and Martian have a primary key, which is a foreign key to the
parent table (and the parent table has only one field: autonumber). Now, I
want to insert a new record in 'android' or 'Martian.
This can be done easily using MS-Access forms. But I want to do so using
SQL. To add a Martian, I first need the parent ID. How do I know what it
is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent.
How do I do that, if I don't know the autonumber value of the record?

Many thanks for your help
Ilan


"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com>
wrote in message news:ne********************@lnews.actcom.co.il...

I have a 'supertype' table with only one field: autonumber. Call this table the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give birth to identical mules.
So each android and a martian have primary key which is a foreign key to the parent table. Now, I want to insert a new record in 'android' or 'martian'. This can be done easily using MS-Access forms. But I want to do so using
SQL. To add a martian, I first need the parent ID. How do I know what it
is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent.
How do I do that, if I don't know the autonumber value of the record?

Many thanks for your help
Ilan

[Rick Brandt]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com>
wrote in message news:ne********************@lnews.actcom.co.il...

Apologies for the early post before proof reading. Here is my post again,
proof read:

I have a 'supertype' table with only one field: autonumber. Call this table
the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Androids and Martians have only one thing in common: they can unilaterally
birth to mules.
Each android and Martian have a primary key, which is a foreign key to the
parent table (and the parent table has only one field: autonumber). Now, I
want to insert a new record in 'android' or 'Martian.
This can be done easily using MS-Access forms. But I want to do so using
SQL. To add a Martian, I first need the parent ID. How do I know what it
is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent.
How do I do that, if I don't know the autonumber value of the record?

Many thanks for your help
Ilan


"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com>
wrote in message news:ne********************@lnews.actcom.co.il...

I have a 'supertype' table with only one field: autonumber. Call this

table

the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Martian have only one thing in common: they give birth to identical mules.
So each android and a martian have primary key which is a foreign key to

the

parent table. Now, I want to insert a new record in 'android' or

'martian'.

This can be done easily using MS-Access forms. But I want to do so using
SQL. To add a martian, I first need the parent ID. How do I know what it
is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent.
How do I do that, if I don't know the autonumber value of the record?

Sorry, but I fail to see why you need the parent table at all. Why not just
have AutomNumber PKs on each of the other tables? Are the fields in the other
tables different from each other?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Ilan Sebba]

"Sorry, but I fail to see why you need the parent table at all. Why not
just

have AutomNumber PKs on each of the other tables? Are the fields in the other tables different from each other?"
Each androids can give birth to many mules. Each martians can also give
birth to many mules. So the mules has a many-to-one relationship with both
androids and martians. So I have a choice: I can either have two foreign
keys in the mule table, one to androids and one to martians. I was advised
that this is not good practice, and it is better to have the 'androids' and
martians as subtypes to a parent supertype which contains their 'common'
fields. Unfrotunately, the only thing they have in common is that they give
birth to mules.

Is this starting to make sense?

Many thanks for bearing with me.

Ilan
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bp*************@ID-98015.news.uni-berlin.de...
"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com> wrote in message news:ne********************@lnews.actcom.co.il...

Apologies for the early post before proof reading. Here is my post again, proof read:

I have a 'supertype' table with only one field: autonumber. Call this table the 'parent' table. There are two subtypes, 'androids' and 'martians'.
Androids and Martians have only one thing in common: they can unilaterally birth to mules.
Each android and Martian have a primary key, which is a foreign key to the parent table (and the parent table has only one field: autonumber). Now, I want to insert a new record in 'android' or 'Martian.
This can be done easily using MS-Access forms. But I want to do so using SQL. To add a Martian, I first need the parent ID. How do I know what it is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent. How do I do that, if I don't know the autonumber value of the record?

Many thanks for your help
Ilan


"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com> wrote in message news:ne********************@lnews.actcom.co.il...

I have a 'supertype' table with only one field: autonumber. Call this table

the 'parent' table. There are two subtypes, 'androids' and 'martians'. Martian have only one thing in common: they give birth to identical mules. So each android and a martian have primary key which is a foreign key to

the

parent table. Now, I want to insert a new record in 'android' or

'martian'.

This can be done easily using MS-Access forms. But I want to do so

using SQL. To add a martian, I first need the parent ID. How do I know what it is?

Here are my tables

tlbParent
Autonumber (PK)

tblAndroid
ParentFK (PK)
AndroidFields

tblMartian
ParentFK (PK)
MartianFields

tblMules
ParentFK
MuleFields

If I want to insert an Android, I need to first insert a record in parent. How do I do that, if I don't know the autonumber value of the record?

Sorry, but I fail to see why you need the parent table at all. Why not

just have AutomNumber PKs on each of the other tables? Are the fields in the other tables different from each other?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com