Updating Relational Database

[Guest]

I created a database with a Master Table of parts related many to one with a
table of departments. They are related on a Dept_ID field which is a number
long integer format. I used update queries for each of the 52 departments to
originally populate the Dept_ID field in the parts tablewhich has around 4200
records in it.

This Master Parts table originally came from a text file generated by an
outside company. I have just been given access to the outside companies
database of our parts with the software to generate the parts table updated
every month.

I have created an import spec and am using the “TransferText†Method to
bring in the new table, but I would like to set it up so the new table
replaces the old table preserving the relationships already created in my
database.

Two questions: 1) I guess I could sequence 50 separate update queries after
the TransferText line in my code but is there a better way? And 2)Is this an
uphill battle I am fighting attempting to replace the main table on a regular
basis while maintaining the base structure of the database?

As Always Thank you for any ideas

 

[Guest]

I'm a little surprised that the relationship is one-to-many rather than
many-to-many. It implies that no part is used by more than one department.
Even if this is true then by modelling the relationship as if it were a
many-to-many one by means of another table with foreign key columns Part_ID
and Dept_ID would mean that replacing the Parts table each time with an
updated one would leave the relationships undisturbed, assuming the Part_ID
(or whatever the primary key of the Parts table is called) retains the same
values. You might then have to weed out any orphaned relationships by
deleting unmatched rows from the 'junction' table but that's a trivial task.

 

[Guest]

No, sorry I wasn't clear. Many Parts to One Department. Related together on
a Dept_ID number that matches an Account Number. I import the data into a
parts table which has text names of the Departments but not the Dept_ID
number. I was looking for the most efficient way of updating the Dept_ID
field rather than running a separate update query for each department in the
VB Sequence.

 

[John Vinson]

No, sorry I wasn't clear. Many Parts to One Department. Related together on
a Dept_ID number that matches an Account Number. I import the data into a
parts table which has text names of the Departments but not the Dept_ID
number. I was looking for the most efficient way of updating the Dept_ID
field rather than running a separate update query for each department in the
VB Sequence.

Join on the Department Name (you may need to index it uniquely in the
departments table, but I presume you won't have two departments with
the same name but different ID's), and update to
[Departments].[DepartmentID].

John W. Vinson[MVP]

 

[Guest]

Thank you very much, the simple things in front of my eyes still get the best
of me.

No, sorry I wasn't clear. Many Parts to One Department. Related together on
a Dept_ID number that matches an Account Number. I import the data into a
parts table which has text names of the Departments but not the Dept_ID
number. I was looking for the most efficient way of updating the Dept_ID
field rather than running a separate update query for each department in the
VB Sequence.

Join on the Department Name (you may need to index it uniquely in the
departments table, but I presume you won't have two departments with
the same name but different ID's), and update to
[Departments].[DepartmentID].

John W. Vinson[MVP]