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
----- Original Message -----
From: "captjeanbill" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Saturday, August 13, 2005 11:37 AM
Subject: [AccessDevelopers] Data transfer between tables
> I have a new project that is all set up with multiple related
> tables. Granted it has some problems, and I've asked for a few
> solutions here, but now I want to transfer the real data into the new
> project. There is already some data there, I just need to be able to
> add to it.
>
> Now, here is my problem. The old data has one table with all of the
> information contained within it. The new project splits the data up
> between three related tables.
>
> As I have practically no experience in VBA code, how do I keep track
> of the proper table to copy the fields into? And how do I keep the
> related tables together. I don't know how to pull out the
> appropriate ID numbers to reference in the next table.
>
> Here how it is laid out in the new project.
> Tbl_Owner
> Tbl_BoatSpec -- linked to Tbl_Owner with an
> OwnerID field
> Tbl_Event --linked to Tbl_BoatSpec
> with a SpecID field.
>
>
> Each owner can have multiple boats so that first relationship is one-
> to-many.
> The second table lists all of the specifications and measurements of
> each boat, one record per boat.
> Each boat can be entered in multiple Events, so that relationship is
> also one-to-many.
>
> Which record in which table needs to be created first for the
> automatic relationships to work?
>
> Once I figure out how to just transfer the data, I can then work on
> improving the transfer by doing a search on an existing Owner. If
> the Owner already exists, I'll need to use that OwnerID instead
> of
> creating a new Owner record. Likewise, if the Boat also exists, all
> I need to do is create a new Event record and link it to the SpecID.
>
> Can anyone get me started on referencing these tables? I'll be
> using
> either a linked or imported table that contains the old data.
>
> Any help is greatly appreciated. -- Jeanie
>
>
>
>
>
>
>
> 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=12hbgjg07/M=362335.6886445.7839731.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123967789/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/
