Solved

Loading Data from Excel

Posted on 2011-03-11

Microsoft Access

1 solution

325 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 500 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

Clever Gmail Targeting Phishing Email Discovered

Article by: Dermot

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…

Splitting Microsoft Access Database with Multiple User

Article by: Jason

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…

Using a SQL Server View from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

MS Access – Basic Query Design

Video by: Jim

Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.