Bill-
How were your tables related before you started to implement the new design? I imagine each of the "n" tables had some common foreign key field such as CustomerID to relate the policies to the owner.
To easily build the new tables, you'll probably need to write some code. Start with an empty "common" table. Add a foreign key field - ComcomID to each of your existing policy tables. If ComcomID in your common table is AutoNumber, your code might look like:
Dim db As DAO.Database, rstCom As DAO.Recordset, rstPolicy As DAO.Recordset
Dim lngID As Long
Set db = CurrentDb
' Open the common table for insert only
Set rstCom = db.OpenRecordset("SELECT * FROM tblCommon", dbOpenDynaset, dbAppendOnly)
' Open the policy recordset
Set rstPolicy = db.OpenRecordset("SELECT * FROM tblLifeInsurance", dbOpenDynaset)
' Loop and process each policy
Do Until rstPolicy.EOF
' Start a new common record
rstCom.AddNew
' Copy the common fields
rstCom!PolicyType = "Life"
' Can grab the new AutoNumber as soon as row is dirtied
lngID = rstCom!ComcomID
' Copy other fields …
rstCom!NextField = rstPolicy!NextField
' …. Other 5 or 6 fields here...
' Save the common record
rstCom.Update
' Edit the policy record
rstPolicy.Edit
' Set the new link field
rstPolicy!ComcomID = lngID
' Save it
rstPolicy.Update
' Get the next one
rstPolicy.MoveNext
Loop
' Clean up
rstPolicy.Close
rstCom.Close
Set rstPolicy = Nothing
Set rstCom = Nothing
Set db = Nothing
You'll have to tweak that code for the table names and the names of the 5 or 6 "common" fields you need to copy.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 13, 2014, at 2:30 AM, <Bill.Singer@at-group.net> <Bill.Singer@at-group.net> wrote:
John V.
I just thought of a problem. I have created my table with all the common information for all my separate lines of insurance. I was just going to use my current separate tables for the rest of the information because all of the information is already there. I will just eventually delete the fields that I do not use. However, I just realized that in order to create a 1 to 1 relationship I will have to have a field in my current tables that matches that ID field (ComcomID) in my table with all the common info. Adding the field in the table is easy but how do I get the correct number in that field. Basically I am having to append one field in a record to match the ID from another table.
Clear as mud.
Bill
MN
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar