Appending and updating tables

[Guest]

I have a database with 11 tables for various departments. All the tables
contain the same fields but each department has items which are uniquely
identified. I have 3 questions, firstly, i would like to create one table
with all the data from the 11 combined, tried append query and update query
with no luck (operator error I think). In each table the unique field is the
primary key and also autonumber. I need to keep the number the same in the
new table.
Secondly I want to create a form which when I add a new record with the
correct Department will autonumber correctly according to the relevant table.
Lastly I want the new table to update correctly with the new records.
Is this a big job or am I just a muppet?

 

[John Spencer]

Not a trivial task.

You can only have one autonumber field per table and the autonumber values
in that field must be unique (except for rare glitches). So you can't have
the same number from two different tables going into the primary key field.

Your new table might have fields in it to hold the old table autonumber
value and an identifier field telling you which of the eleven tables it
originally came from.

Combined Table with fields
NewPrimaryKey (Autonumber)
OldPrimaryKey (Number: Type Long)
DeptName (Text)
and then all the other fields that you need

When you append to the combined table you wouldn't append anything to the
NewPrimaryKey (whatever name you give it). You would append the old
autonumber field into the new tables OldPrimaryKey field.

The SQL view of the append query would look somethng like

INSERT INTO [CombinedTable] (OldPrimaryKey, DeptName, FieldA, ...)
SELECT PrimaryKey, "DepartmentA" as SourceField, FieldA, ....
FROM [DepartmentA]

Once you have done that you will need to fix the data in related tables by
updating the foreign key fields to point to the new primary key field. You
would need to use an update query for this purpose.

UPDATE RelatedTable Inner Join CombinedTable
ON RelatedTable.ForeignKey = CombinedTable.OldPrimaryKey
SET RelatedTable.ForeignKey = CombinedTable.PrimaryKey
WHERE CombinedTable.DeptName= "DepartmentA"

After you have done that, then add a new form based on the CombinedTable for
your data entry.

WARNING: MAKE a backup of your database tables BEFORE you do this. If you
make a backup you can get back to where you were if things go wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..