Results 1 to 7 of 7
  1. #1
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    import ot multiple linked tables

    Hi

    have loads of spreadsheets that I want to import into a linked table structure.

    I can see three scenarios:

    split the spreadsheet into sections and import each section into the database table

    import the spreadsheet into a table and then run multiple updates to the linked fields - not sure how that would keep the key structure

    create a query using fields from all tables and then run an append on the query - is this even possible?

    would appreciate any thoughts

    thanks

    Ian

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps it's just me, but I'm not following the issue.

    You have a number of spreadsheets -are these all same format?
    What exactly differentiates one from another?

    You mention
    the database table
    Can you tell us what this really means and represents?

    A general approach that is often used to get data from spreadsheets or text files..

    -Read the data into a temporary file that accommodates all the fields to be imported. Add a couple of more fields to represent the Source of the data , and the date/time of the import.

    - create a program to review or validate the incoming data to ensure mandatory fields are present and acceptable values ---write any/all errors to a log along with the Source, date/time info. Determine corrective action logic for correcting bad/missing data.

    -then have a series of queries to extract the fields necessary to populate table X, and another query for table Y etc. Again log any issues so you can correct things without stopping the process.

    If you have a tested data model for your production database and have set referential integrity, you will need to populate parent tables before you populate child tables (otherwise you'll get errors )

    Good luck.

  3. #3
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I would use the Import Linked Table function to reference the Excel Spreadsheets. When you go through the Link Wizard it should allow you to specify a Data link instead of import, as well as allowing you to specify which sheet the data comes from.

  4. #4
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi
    thanks for the advice

    I'm still at the design stage in that I have 160 spreadsheets to import but I'm using one to test the design concept etc.

    The database will consist of five main tables births, banns, marriages, christenings and deaths, each of the tables will relate to all events of that type

    At the moment there is a single spreadsheet for every birth, banns, marriage and death at a specific church. So each church has five spreadsheets.

    Off to do some more testing

    cheers

    Ian

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ian,

    Please show us jpg of your relationships window.

    It sounds like you have

    People
    Locations (which may be subdivided to Parish and Church and Town/Village???)
    Events(births, banns, marriages, christenings and deaths)

  6. #6
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453

    relationships

    HI

    heres a jpeg of my proposed relationship.

    Having considered the advice I've been given thought I'd use one set of tables and filter my my results from that set.

    Not sure if I'd be better to have separate tables for birth, banns, marriage and death as the records have some differences?

    The records also include fiche numbers that contain \ and / but I'm assuming that as they will be stored as text this will not be an issue?

    thanks

    Ian
    Attached Thumbnails Attached Thumbnails relationship.jpg  

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ian,
    Here is a free data model from Barry Williams' site that may give you additional info re relationships and tables.

    Relationships should be derived from your Business rules. There are many links discussing business rules and their role in database design.
    Here are a few:
    http://www.databaseanswers.org/tutor...ness_rules.htm
    http://www.databasedesign-resource.c...ess-rules.html
    http://etutorials.org/SQL/Database+d...usiness+Rules/

    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2014, 03:53 PM
  2. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  3. Multiple linked tables query
    By cchampagne17 in forum Queries
    Replies: 3
    Last Post: 07-31-2012, 07:42 PM
  4. Redirect multiple Linked Tables using VBA
    By Wiggles8831 in forum Access
    Replies: 4
    Last Post: 02-08-2012, 11:38 AM
  5. Replies: 5
    Last Post: 02-02-2012, 06:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums