Linked Tables Append Query(ies)

[BobC]

I have 4 linked tables. IDs are auto created in each table
Table 1 - Primary Site (ID, Primary Site Number, Primary Site Name)
* (ID linked to Primary Site ID)
Table 2 - Sub-Sites (ID, Primary Site ID, Sub-Site Number, Sub-Site
Name, …. Etc.)
* (ID linked to Sub-Site ID)
Table 3 - Material Category (ID, Sub-Site ID, Category Name, … Etc.)
* (ID linked to Material Category ID)
Table 4 – Site Materials (ID, ID linked to Category ID, Model Number,
Serial Number, …. Etc.)
The is a basic set of Material Categories along with a basic set of
standard Site Materials at all sites.
As it now stands, in order to add a new Primary Site or a new Sub-Site
with a standard Material Categories and a basic set of equipment, I
must ‘MANUALLY’ append records to each table ‘in sequence’.
IS THERE A SIMPLIER METHOD SUCH AS USING SOME FORM OF APPEND QUERY(ies)
as I am talking hundreds of items to be added.

 

[Tom van Stiphout]

On Wed, 07 Oct 2009 19:02:18 -0400, BobC <[email protected]>
wrote:

Yes, you must do this "in sequence" so you are not violating
referential integrity.
You didn't state this, but I am assuming you are trying to import data
from some Source tables into this database. Indeed you need to do this
one table at a time, so it will take 4 append queries to import all.
The way I approach this is by first importing the top-level table:
Table1. This would be a simple append query:
(I'm using a bit of shorthand)
insert into Table1(PSNumber, PSName)
select PSNumber, PSName from mySourceTable1

Then to import Table2 you need to know the ID values in Table1 that
were generated. So you join mySourceTable2 with Table1. I am assuming
that mySourceTable2 either has the PrimarySiteNumber (and I am
assuming this is Unique) or you can join with mySourceTable1 to get
that value. Then the append query becomes trivial and you can use
Table1.ID for the value in Table2.PrimarySiteID.

Repeat same approach for Table3, 4.

-Tom.
Microsoft Access MVP

 

[BobC]

Thank you for your guidance!
I may need to do this on a repetative bases? Would this be something you
think I could do with a Macro or would I need to use VB code?
Bob

 

[Tom van Stiphout]

Since this only requires running 4 queries in a row, you can certainly
use a macro (OpenQuery) to do so. Or use VBA such as CurrentDB.Execute
"myQuery1", dbFailOnError

-Tom.
Microsoft Access MVP

 

[BobC]

Thank You!!! ...
Please keep and eye out .. I may need some additional guidance
Bob