Wow, thanks Toby. I'm going to give these tips a try and I'll get back to you. I did not know about the INSERT INTO line. What will happen with the Select Distinct Owner if it detects that the Owner already exists?
I had already determined the order in which I had to create the new records. I manually copied the old table Owner's name into the new tbl_Owner table. That automatically created the OwnerID. I then, without sorting, copied those new OwnerID fields in the new records into tbl_BoatSpec to get the link set up. Then I went back to the Old table and copied all of the fields I needed for measurements into the appropriate fields in tbl_BoatSpec. Last step was to copy the SpecID from tbl_BoatSpec into the Event table and then go back to the old table and copy the appropriate fields. It all worked, except I didn't do any tests for existing owners or existing owner-boat combinations (which does happen). I manually just went through and fixed those so that I could at least get this database up and running. It will need to be automated however. This time I manually was able to copy and paste 225 records. So, that's where I stand. Thanks again, Toby for your help. -- Jeanie Sunny Florida --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> wrote: > I can't offer you too many specifics, but an overview (and order) of what > you need to do is: > > 1. Fill the Tbl_Owner table with all the Owners from the old table > 2. Fill the Tbl_BoatSpec table with all the boats, inserting the > appropriate OwnerID from Tbl_Owner > 3. Fill the Tbl_Event table with all the events, inserting the appropriate > SpecID from Tbl_BoatSpec > > For 1, do a > INSERT INTO Tbl_Owner(Field1, Field2,..) Select Dinstinct Owner, > otherfield1, otherfield2 FROM OldTable > > Hopefully, you don't have any owners or boats with duplicate names. That > is, 2 owners with the exact same name. > > For 2, it gets a little more tricky to fill in the OwnerID field. There are > two options: > A. Use VBA to open OldTable into a recordset and run a query to figure out > the OwnerID while building your Insert Into BoatSpec query > B. Add an OwnerName field that you fill first while leaving the OwnerID > blank, then run a second query filling the OwnerID field, finally delete > OwnerName field > > I'm guessing option A is not an option without significant VBA examples > > For option B, you could exactly like you did for 1, except that you Insert > the OwnerName into Tbl_BoatSpec. Then you fill OwnerID > INSERT INTO Tbl_BoatSpec ("OwnerID") Select Tbl_Owner.OwnerID From Tbl_Owner > INNER JOIN Tbl_BoatSpec ON TblOwner.OwnerName = Tbl_BoatSpec.OwnerName > Then delete the OwnerName field from BoatSpec > > Follow a similar course of action for 3 as you just used for 2. > > Clear as mud? > > HTH, > Toby > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12htk37af/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124117530/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
