j.t.w@juno.com wrote:
> You are absolutely correct about importing the data where "the person
> information (columns A, B & C) would go in one table with column A as
> the primary key and the payments would go in another table (columns A
&
> D) where each payment was a separate record".
>
> How would I go about
> preparing the excel spreadsheet to import the data as you suggest?

No preparation required. See the below code:

Sub test()
Set con = CreateObject("ADODB.Connection")
With con
..Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
..Execute _
"CREATE TABLE ReferencedTable (" & _
" employee_ID INTEGER NOT NULL," & _
" lname VARCHAR(35) NOT NULL," & _
" fname VARCHAR(35) NOT NULL," & _
" CONSTRAINT pk__ReferencedTable" & _
" PRIMARY KEY (employee_ID));"
..Execute _
"CREATE TABLE ReferencingTable (" & _
" employee_ID INTEGER NOT NULL," & _
" occurence INTEGER NOT NULL," & _
" earnings CURRENCY NOT NULL," & _
" CONSTRAINT pk__ReferencingTable" & _
" PRIMARY KEY (employee_ID,occurence)," & _
" CONSTRAINT fk__ReferencingTable_ReferencedTable" & _
" FOREIGN KEY (employee_ID)" & _
" REFERENCES ReferencedTable (employee_ID)" & _
" ON UPDATE CASCADE ON DELETE CASCADE);"
..Execute _
"INSERT INTO ReferencedTable" & _
" (employee_ID, lname, fname)" & _
" SELECT F1 AS employee_ID," & _
" F3 AS lname, F2 AS fname FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$];"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 1 AS occurence, F4 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F4=0 OR F4 IS NULL);"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 2 AS occurence, F8 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F8 = 0 OR F8 IS NULL);"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 3 AS occurence, F12 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F12 = 0 OR F12 IS NULL);"
..Close
End With
End Sub

The table/column names are for demo purposes; choose alternatives
appropriate to your data. Note it would be better to use start_date and
end_date columns in place of my single occurrence column. Using the
occurrence column may make future INSERTs more difficult e.g. to find
the MAX(occurance) for the employee_ID, perhaps best done in a
PROCEDURE.

Jamie.

--