Solved

Loading Data from Excel

Posted on 2011-03-11
1
315 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:gcgcit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35107798
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 ....
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question