[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

Solved

Loading Data from Excel

Posted on 2011-03-11

Microsoft Access

1 solution

Medium Priority

339 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

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

Join The Community

1 Comment

LVL 85

Accepted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points

ID: 351077982011-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 ....

Featured Post

Question has a verified solution.

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

Dynamic Printer Selection for Access

Article by: Helen

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …

QODBC: How to use VBA to Disable Non-Updateable QuickBooks® Fields on a Microsoft® Access® Form (Part 1)

Article by: Annaliese

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.

MS Access – Different Ways to Start Up a Database

Video by: Jim

With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

Microsoft Access

How To Make a Graph with Microsoft Access

Video by: crystal

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…