Re: Importing into multiple linked tables

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

"markmarko" <markmarko@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D0D665B1-A32E-4DE5-807E-2A85CFC351EA@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: New record on a new day (re-post)
    ... In a well-normalized relational database design, ... CommentTypeID (a foreign key, pointing back to the Primary Key in a ... a single row in a table. ... be too difficult to maintain this data in an excel spreadsheet, ...
    (microsoft.public.access.formscoding)
  • Re: Changing Path for table linked to spreadsheet
    ...  I have a database with a table in it which is ... linked to an excel spreadsheet. ... strFileLocation as the generic variable. ... DoCmd.DeleteObject acTable, "xlimportsurvey" ...
    (comp.databases.ms-access)
  • Re: Importing data from excel spreadsheet
    ... Some data is contained in a database that has been ... data from the Excel spreadsheet into the database. ... Excel spreadsheet does not contain an autonumber. ... can then create Append and Update queries based on the spreadsheet ...
    (microsoft.public.access.tablesdbdesign)
  • Mail merge macro: select recipients from Excel during merge
    ... because I'm using an Excel spreadsheet as my database. ... Private Sub CommandButton4_Click ... ' Macro recorded 5/11/2008 by Peggy Duncan ...
    (microsoft.public.excel.programming)
  • Re: Auto import data from spreadsheet
    ... Iam trying to create a link between an Excel spreadsheet and an Access table. ... You can't import multiple worksheets simultaneously, ... >>> there's a table in your database corresponding to each of these? ... >>> by importing with a query instead of with the usual TransferSpreadsheet ...
    (microsoft.public.access.externaldata)