Select Distinct Owner returns a list with no duplicate Owners. I was assuming that a single owner could own multiple boats, in which case Select Owner would return a list with the same owner in multiple locations in the list.
If this is a one time thing where you only have 225 records, I wouldn't bother to automate. Sounds like you might have completed this, but the steps to perform the transfer manually can be summed up as: 1. Copy desired fields from ALL records from OldTable to each of the three new tables (keeping same order in all cases) 2. Wherever there are duplicate OwnerID's, manually change the OwnerID field in the Tbl_BoatSpec table to the first instance of that OwnerID in the Tbl_Owner table 3. Delete the duplicate OwnerID records out of the Tbl_Owner table 4. Repeat 2 & 3 for the BoatSpecID's in the Tbl_BoatSpec and Tbl_Events tables HTH, Toby ----- Original Message ----- From: "Jeanie S" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Monday, August 15, 2005 5:52 AM Subject: [AccessDevelopers] Re: Data transfer between tables 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 > > > Please zip all files prior to uploading to Files section. Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12hqq2lhs/M=362335.6886445.7839731.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124134483/A=2894361/R=0/SIG=13jmebhbo/*http://www.networkforgood.org/topics/education/digitaldivide/?source=YAHOO&cmpgn=GRP&RTP=http://groups.yahoo.com/";>In low income neighborhoods, 84% do not own computers. At Network for Good, help bridge the Digital Divide!</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/
