I actually did the manual sequence as you described for these 225 records, but it will be an ongoing task for each new Event throughout the year. This just happens to be our biggest Event. I will now have time to automate it before the next event in the Spring, so I have a little breathing room.
I have taken out all of the instances I saw of duplicate Owner names. Can I or should I, now change the properties so that the Owner Name field is Distinct? I'm going to start working on that automation this evening or tomorrow. I'll let you know how I do. -- Jeanie --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> wrote: > 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=12hbju7i3/M=362329.6886308.7839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124158713/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992 ">Fair play? Video games influencing politics. Click and talk back!</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/
