Loading Data from Excel

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

gcgcit

Asked: 2011-03-11

Advertise Here

Microsoft Access

1 Solution

LVL 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2011-03-11

You cannot do this directly through the Import feature. You'll have to import the data to a Temporary table in Access, then use VBA/SQL to move the data into tables as needed. Basically, once you import the table, you'd build a recordset based on that imported table, loop through the records, and add them to the "live" tables as you go:

Dim rst As DAO.Recordset
Set rst = Currentdb.Openrecordset("SELECT * FROM MyTempTable")

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, AuditValue) VALUES(" & rst("Employee") & "," & rst("Audit 1") & ")"
'/ and so on through all Audit columns
rst.MoveNext
Loop

Obviously this process would depend largely on your table structure and such ....

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Loading Data from Excel

Join & Write a Comment Already a member? Login.

Featured Post