Importing into multiple linked tables

M

markmarko

Hello!

We are transitioning from keeping data on excel spreadsheets into an access
database. Going forward, all data will be typed directly into database, but I
need to import our previous data for accurate YTD info.

I can see how to import into a single table. How can I import into multiple
tables? The tricky part is --- How do I make the connections? How can I put
the correct foreign key in sales details, for example?
 
R

Roger Carlson

Transforming data from a flat-file into a normalized structure is always a
challenge and there is no single answer. It depends a lot on your specific
circumstances and data. However, I've had some success with this method.

Import the excel spreadsheet into an Access table. If there is a problem
with datatypes between Excel and Access (this does happen sometimes), you
may need to save the Excel as a CSV and import it that way.

Next, use the Table Analyser Wizard (Tools>Analyze>Table) (A2k-A2K3) to
split the table. Make a copy of your table first, because the Wizard
doesn't always work the way you think it should. Play with it until you get
it to work the way you want it to. One of the things it will do for you is
add a foreign key and enter a value to create the relationship. It insists
on creating this as a LookUp field, so I would remove that immediately (the
LookUp property, that is).

Good luck.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
M

markmarko

That's sounds like a good direction to try. I've already imported into a
single table, and that part works ok. The difficulty is in splitting into
separate tables and keeping connections... I'll try that wizard, though it
seemed like you don't have a lot of control over it.
 
P

Peter Hibbs

Mark,

You could try using the Excel to Access Converter Utility program
(also on Roger Carlson's Web site) which will do the conversion
automatically.

HTH

Peter Hibbs.
 
M

markmarko

Thanks, I'll try it...

It seems the Analyzer Wizard will make tables... I already have the
tables... I need the imported data to parse into various, already-existing
tables..... Hmmmph.
 
G

gllincoln

Hi Mark,

Then it sounds as if what you need to do is to build a series of append
queries - moving the data to the appropriate targets.

Gordon
 
G

gllincoln

Hi Mark,

I dunno - I can't see the data from here. <grin>

If you are breaking the data out from a single table then it's easy, just
carry a copy of the primary key of the master table with you. On the other
hand, if the data is already split, and you don't have a foreign key
attached, you are going to have to go back to the drawing board and figure
out some way of mapping that information. Often a combination of columns
that equal the columns in another table can make the connection and then you
can update the foriegn key field with the appropriate primary key value from
the parent table. However, without knowing the tables and the data - I
wouldn't have a clue on offering more specific advice.

Cordially,
Gordon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embed the same Excel multiple times 0
Table Security in Access 2013 1
Help with importing/appending 0
Importing Data and AUTONUM issues 1
Import Excel 2010 table into Access 2010 - not listed as range 0
Importing spreadsheet cells that have multiple values 1
Things to know in import from Excel 1
import xml not a problem, setting key for subtables is 1

Top