Appending data from two tables to two tables in Access 2000.

[Guest]

OK. I have two Part tables in a one-to-many relationship. (Parts and Test
Criteria respectively) Each time I Audit and retest a part I want to pull
the Part and Test Criteria and push it into two Audit tables.

The Audit tables also have a one-to-many relationship. Audit #1 (1.Part,
Lot, Date) on the one side and the test results for Audit #1 (1.Test1,
1.Test2) on the many side.

Using an Append Query I can get the One Side tables to update correctly.
The AutoNumber field increases as it should and the record is fine. I can
now add new Audit data.

First problem is that I can't get the second, many-side table to "APPEND"
correctly. The error I get is because the AutoNumber on the one side,
doesn't maintain its relationship to the many side table during the append
action. I can move the records manually in a separate Append Query but the
records are now orphans. (The relation ship is - a Part Table field called
AUTO, set to AutoNumber and a Result Table field called AUTO, set to Number)

Another problem is the Append Query's "INSERT INTO" statement doesn't like
the idea of defining two tables. The Help file vaguely hints at it but what
I’m trying is not working.

So all I can do now is update the Audit Part table and the Audit Result
table either doesn’t work or creates orphan records.

I understand there will be a small amount of duplicate data passing from one
table to another. Due to constantly changing parts, criteria, etc., I update
records quite often to the first set of tables. So I don't want to link data
that has been changed in the Part table to old records in the Audit table. I
want the criteria that existed at that moment in time to remain in the Audit
table. (I hope that makes sense)

Can a person with basic VBA skills [moderate Form and Report events] get
this done or do I sit down and rethink the whole thing?
Thanks.
Dan

 

[Baz]

OK. I have two Part tables in a one-to-many relationship. (Parts and Test
Criteria respectively) Each time I Audit and retest a part I want to pull
the Part and Test Criteria and push it into two Audit tables.

The Audit tables also have a one-to-many relationship. Audit #1 (1.Part,
Lot, Date) on the one side and the test results for Audit #1 (1.Test1,
1.Test2) on the many side.

Using an Append Query I can get the One Side tables to update correctly.
The AutoNumber field increases as it should and the record is fine. I can
now add new Audit data.

First problem is that I can't get the second, many-side table to "APPEND"
correctly. The error I get is because the AutoNumber on the one side,
doesn't maintain its relationship to the many side table during the append
action. I can move the records manually in a separate Append Query but the
records are now orphans. (The relation ship is - a Part Table field called
AUTO, set to AutoNumber and a Result Table field called AUTO, set to Number)

Another problem is the Append Query's "INSERT INTO" statement doesn't like
the idea of defining two tables. The Help file vaguely hints at it but what
I'm trying is not working.

So all I can do now is update the Audit Part table and the Audit Result
table either doesn't work or creates orphan records.

I understand there will be a small amount of duplicate data passing from one
table to another. Due to constantly changing parts, criteria, etc., I update
records quite often to the first set of tables. So I don't want to link data
that has been changed in the Part table to old records in the Audit table. I
want the criteria that existed at that moment in time to remain in the Audit
table. (I hope that makes sense)

Can a person with basic VBA skills [moderate Form and Report events] get
this done or do I sit down and rethink the whole thing?
Thanks.
Dan

In the [Audit Part] table, create another column (say, part_AUTO) to store
the autonumber (primary key) from the Part table. Then you can run a couple
of queries roughly like this:

INSERT INTO [Audit Part] (some, fields, part_AUTO) SELECT some, fields, AUTO
FROM Part WHERE some criteria

INSERT INTO [Audit Result] (some, fields, AUTO) SELECT some, fields, AP.AUTO
FROM [Test Criteria] TC INNER JOIN [Audit Part] AP ON TC.AUTO = AP.part_AUTO
WHERE some_criteria