I have the following database (see screenshot: data model), I need to import the following data (see screenshot: excel data).
I need to import the data from the excel spreadsheet, but the audit columsn (C thru H) are actually their own records. How do I go about indicating that each Audit creates it's own record. Example first employee's data:
Employee ID 58468
Employee Marie Sampson
She should have 6 individual records in the tblPace
Audit 1
01/01/2010 in the "auditdate" field of tblPace
12 in the "pace" field of tblPace
Audit 2
02/01/2010 in the "auditdate" field of tblPace
14 in the "pace" field of tblPace
Audit 3
3/01/2010 in the "auditdate" field of tblPace
8 in the "pace" field of tblPace
Audit 4
04/01/2010 in the "auditdate" field of tblPace
13 in the "pace" field of tblPace
Audit 5
05/01/2010 in the "auditdate" field of tblPace
15 in the "pace" field of tblPace
Audit 6
06/01/2010 in the "auditdate" field of tblPace
14 in the "pace" field of tblPace
I need to do this for each row in the excel file.
ee-pacemodel.jpeg
ee-pacedata.jpeg
Dim rst As DAO.Recordset
Set rst = Currentdb.Openrecordset("S
Do Until rst.EOF
'/check to see if the Employee exists:
If Nz(Dlookup("EmployeeID", "Employees", "EmployeeID=" & rst("Employee"), 0) <> 0 then
'/does not exist - add the Employee
Else
'/employee does exist
End If
'/add audit data
Currentdb.Execute "INSERT INTO YourAuditTable(EmployeeID,
'/ and so on through all Audit columns
rst.MoveNext
Loop
Obviously this process would depend largely on your table structure and such ....