One tables into two smaller ones

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I received a DB to work on for a Facility. It is a Key inventory. They are
using a table with the following fields. KeyRing, RingLocation, NumberofKeys,
KeyOne, KeyOneLocation, KeyTwo, KeyTwoLocation, and so on up as many as 28
keys on one ring. I need to take this table, divide it into two tables. Table
one KeyRing, KeyRingLocation, and NumberofKeys. the second table of KeyRing,
KeyNumber, KeyLocation. I would have a relation between the two tables with
the KeyRing field.
My problem is how do I get the KeyOne and KeyOneLocations into the KeyNumber
and KeyLocation fields. Is there an easy way to merge this data. Hope you
understamd. Thank you in advance.
 
Mike you described your problem very accurately.

I would write 28 different append queries.

Firstly add your current data to Table one (KeyNumber,KeyRingLocation etc)

then create an append query to add data from KeyRing, KeyOne, KeyOneLocation
to Table two into the corresponding fields. Save the query as AppendKeyOne
leaving it open in design mode, then just swap the fields to KeyTwo and save
it as AppendKeyTwo. It shouldn't take long to create 28 queries.

You may want to use a criteria of Is Not Null to avoid adding empty records
into table two.

Hope the answer is as clear as the question.
 
Back
Top