Can I add records in parent table during daughter table append?

[Guest]

I'm trying to append values into a daughter table, when parent table records
do not exist. I get an error message that records were not added due to a
key violation.

[Parent].[ID] is an AutoNumber primary key field and is the only required
field in this table

[Daughter].[ID] is an AutoNumber primary key field
[Daughter].[Parent_ID] is a foreign key in the daughter table linked to
[Parent].[ID]

In relationships, I have a one-to-many relationship established by linking
[Parent].[ID] to [Daughter].[Parent_ID] with "Enforce Referential Integrity",
"Cascade Update Related Fields", and "Cascade Delete Related Fields" all
checked on.

I have a simple append query appending 3 fields from separate table into the
daughter table. It won't work. Help!!!

 

[Guest]

Check the properties of the fields in the child table, if any of them doesn't
allow null values, or index with no duplication.

 

[John Spencer (MVP)]

With those settings, you must add the parent record before you can add the
daughter record. That is what you have told the database engine and it is doing
what you told it.

Why do you need to add the daughter records before you add the parent record?

 

[Guest]

John: Thanks for your reply. Regarding, your question "Why do you need to
add the daughter records before you add the parent record?", maybe I need to
reword my question. Change "before" to "simultaneously".

I've imported a spreadsheet as a new table. The spreadsheet contains one
row per new parent table record. A few of the fields in the spreadsheet
belong in the daugther table.

Can I use an append query to "simulanteously" update a parent table and
daugther table or do I have to perform 2 separate append queries (append
parent then daugther).

Thanks,

Chris

With those settings, you must add the parent record before you can add the
daughter record. That is what you have told the database engine and it is doing
what you told it.

Why do you need to add the daughter records before you add the parent record?

I'm trying to append values into a daughter table, when parent table records
do not exist. I get an error message that records were not added due to a
key violation.

[Parent].[ID] is an AutoNumber primary key field and is the only required
field in this table

[Daughter].[ID] is an AutoNumber primary key field
[Daughter].[Parent_ID] is a foreign key in the daughter table linked to
[Parent].[ID]

In relationships, I have a one-to-many relationship established by linking
[Parent].[ID] to [Daughter].[Parent_ID] with "Enforce Referential Integrity",
"Cascade Update Related Fields", and "Cascade Delete Related Fields" all
checked on.

I have a simple append query appending 3 fields from separate table into the
daughter table. It won't work. Help!!!

 

[Guest]

John: Second note to explain in detail my problem in more detail.

I have an oil well database that I've created. Unfortunately, multiple data
sources that I will be importing into this database, don't share a common
unique well identifier (UWI).

I created the parent table [tbl_Well_Header] in my database, with an
AutoNumber primary key [Well_ID]. To address the problem of different UWI's,
I created a daughter table [tbl_Well_UWIs] with the following fields...

[tbl_Well_UWIs].[UWI_ID] --> AutoNumber primary key
[tbl_Well_UWIs].[Well_ID] --> foreign key from [tbl_Well_Header]
[tbl_Well_UWIs].[UWI_Value] --> UWI from external data source
[tbl_Well_UWIs].[UWI_Source] --> Name of data source

So here is the deal. I've linked to an external data source (spreadsheet).
The spreadsheet contains one-line per well record. All of this data needs to
go into my [tbl_Well_Header] table, except the external UWI which needs to go
into the daughter table in the field [UWI_Value].

For any given external data source there is a 1-to-1 relationship between
[tbl_Well_Header].[Well_ID] and [tbl_Well_UWIs].[UWI_Value]

I need to create the link between new [tbl_Well_Header].[Well_ID] records
and [tbl_Well_UWIs].[UWI_Value] on the "fly" during append if possible.

The only alternative I can think of is to make my [tbl_Well_Header] table
non-normal, by adding fields [UWI_source1], [UWI_source2], etc.

Should I take the non-normal approach?

PetroChris

With those settings, you must add the parent record before you can add the
daughter record. That is what you have told the database engine and it is doing
what you told it.

Why do you need to add the daughter records before you add the parent record?

I'm trying to append values into a daughter table, when parent table records
do not exist. I get an error message that records were not added due to a
key violation.

[Parent].[ID] is an AutoNumber primary key field and is the only required
field in this table

[Daughter].[ID] is an AutoNumber primary key field
[Daughter].[Parent_ID] is a foreign key in the daughter table linked to
[Parent].[ID]

In relationships, I have a one-to-many relationship established by linking
[Parent].[ID] to [Daughter].[Parent_ID] with "Enforce Referential Integrity",
"Cascade Update Related Fields", and "Cascade Delete Related Fields" all
checked on.

I have a simple append query appending 3 fields from separate table into the
daughter table. It won't work. Help!!!

 

[John Spencer (MVP)]

No matter what you do, you have to add the Mother records before you add the
daughter records and it has to be in two separate queries (at least as far as I know).

What you can possibly do is to use a transaction to do this action inside of
some vba code. Try reading up on transactions and then post back with some
questions if you have difficulty implementing that strategy.

John: Thanks for your reply. Regarding, your question "Why do you need to
add the daughter records before you add the parent record?", maybe I need to
reword my question. Change "before" to "simultaneously".

I've imported a spreadsheet as a new table. The spreadsheet contains one
row per new parent table record. A few of the fields in the spreadsheet
belong in the daugther table.

Can I use an append query to "simulanteously" update a parent table and
daugther table or do I have to perform 2 separate append queries (append
parent then daugther).

Thanks,

Chris

With those settings, you must add the parent record before you can add the
daughter record. That is what you have told the database engine and it is doing
what you told it.

Why do you need to add the daughter records before you add the parent record?

I'm trying to append values into a daughter table, when parent table records
do not exist. I get an error message that records were not added due to a
key violation.

[Parent].[ID] is an AutoNumber primary key field and is the only required
field in this table

[Daughter].[ID] is an AutoNumber primary key field
[Daughter].[Parent_ID] is a foreign key in the daughter table linked to
[Parent].[ID]

In relationships, I have a one-to-many relationship established by linking
[Parent].[ID] to [Daughter].[Parent_ID] with "Enforce Referential Integrity",
"Cascade Update Related Fields", and "Cascade Delete Related Fields" all
checked on.

I have a simple append query appending 3 fields from separate table into the
daughter table. It won't work. Help!!!

 

[John Spencer (MVP)]

NO, I would advise you not to take the non-normal approach. It usually leads to
lots of problems, extra code, extra data entry, ...

John: Second note to explain in detail my problem in more detail.

I have an oil well database that I've created. Unfortunately, multiple data
sources that I will be importing into this database, don't share a common
unique well identifier (UWI).

I created the parent table [tbl_Well_Header] in my database, with an
AutoNumber primary key [Well_ID]. To address the problem of different UWI's,
I created a daughter table [tbl_Well_UWIs] with the following fields...

[tbl_Well_UWIs].[UWI_ID] --> AutoNumber primary key
[tbl_Well_UWIs].[Well_ID] --> foreign key from [tbl_Well_Header]
[tbl_Well_UWIs].[UWI_Value] --> UWI from external data source
[tbl_Well_UWIs].[UWI_Source] --> Name of data source

So here is the deal. I've linked to an external data source (spreadsheet).
The spreadsheet contains one-line per well record. All of this data needs to
go into my [tbl_Well_Header] table, except the external UWI which needs to go
into the daughter table in the field [UWI_Value].

For any given external data source there is a 1-to-1 relationship between
[tbl_Well_Header].[Well_ID] and [tbl_Well_UWIs].[UWI_Value]

I need to create the link between new [tbl_Well_Header].[Well_ID] records
and [tbl_Well_UWIs].[UWI_Value] on the "fly" during append if possible.

The only alternative I can think of is to make my [tbl_Well_Header] table
non-normal, by adding fields [UWI_source1], [UWI_source2], etc.

Should I take the non-normal approach?

PetroChris

With those settings, you must add the parent record before you can add the
daughter record. That is what you have told the database engine and it is doing
what you told it.

Why do you need to add the daughter records before you add the parent record?

I'm trying to append values into a daughter table, when parent table records
do not exist. I get an error message that records were not added due to a
key violation.

[Parent].[ID] is an AutoNumber primary key field and is the only required
field in this table

[Daughter].[ID] is an AutoNumber primary key field
[Daughter].[Parent_ID] is a foreign key in the daughter table linked to
[Parent].[ID]

In relationships, I have a one-to-many relationship established by linking
[Parent].[ID] to [Daughter].[Parent_ID] with "Enforce Referential Integrity",
"Cascade Update Related Fields", and "Cascade Delete Related Fields" all
checked on.

I have a simple append query appending 3 fields from separate table into the
daughter table. It won't work. Help!!!