Results 1 to 3 of 3
  1. #1
    ArticCat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    1

    Importing data to a normalized database

    I have a large (1.2GB) database of ship data. Each record has fields such as date, part number, product category, quantity and so on for 20 fields. I want to normalize this database to save space and enforce integrity. However, I get new data once a month in a large Excel spreadsheet. My problem is I can not understand how I add big chunks of new data to the tables of a normalized database. It's the concept of doing this that escapes me. Can anyone tell me if this is even possible and, if so, how? Thanks in advance.

  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
    Where are you in this project?
    You mention Normalize this database. Do you have a data model?
    Do you have a database background?

    You may get a lot of good database info from these 4 videos.

    Intro to Databases
    The relational Model
    Data Modeling and ER Diagram
    Database Design

    Getting the database designed to meet and support your requirements is critical to a successful database application.
    Good luck.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum..
    It's the concept of doing this that escapes me. Can anyone tell me if this is even possible and, if so, how?
    Yes it can be done. The how is the rub.
    How experienced in you in RDBMs?


    Do as orange suggests and watch the youtube videos.


    Sooooo, the rub.......
    At the 30,000 foot level, you have tables that are related. Your data comes to you in a spreadsheet, with one row that needs to be logically broken up into pieces that go into many tables. And you do this for many rows.

    In my case, I get data in a csv file. It has 36 "columns" of data. and on average , there are approximately 20,000 lines (rows) to handle each month. One of my import algorithms (fancy way of saying my process) is 60 pages (letter size) long. Tow more import routines are each about 20 pages long. No way I could handle that without errors. So VBA it is.

    There is a lot of processing, like scrubbing the data to ensure it is valid. Can't really trust the data, so lots of validation happens.

    If you can do the processing manually, you can write an algorithm to handle your data. In your case, say there is a table for "Ships". This would be like "Customer" data. You only allow a customer's name in the table once. That ship (customer) is linked to the other tables via a PK-FK relationship. IF that ship is in the table, fine, move on to the next bit of data. Keep doing that until all of the data in that row is handled. Go to the next row and do the same thing.

    Start out with pencil and paper or a whiteboard or sticky notes on the wall and create your tables. The sticky notes are the easiest to move the fields around, but takes lots of wall space.
    Once you think you have the tables normalized, walk through adding data to the tables. Does it fit and seem correct?


    Then you can start typing in a computer and create queries and forms. And start ... and ..

    Post back with questions. Enjoy.....




    large (1.2GB) database
    Edit: you do know that Access has a limit of 2GB per database???
    Have you done a "Compact and Repair" lately?
    And you have a current back up before you do the C & R??

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

Similar Threads

  1. Database not completely normalized
    By cuddles in forum Access
    Replies: 5
    Last Post: 06-10-2014, 05:24 PM
  2. should all data be normalized
    By tagteam in forum Access
    Replies: 7
    Last Post: 09-11-2013, 02:08 PM
  3. Replies: 1
    Last Post: 07-16-2012, 02:10 PM
  4. Create a form from a normalized database
    By nchesebro in forum Forms
    Replies: 2
    Last Post: 01-19-2011, 12:52 PM
  5. Form based on normalized database
    By nchesebro in forum Reports
    Replies: 2
    Last Post: 01-14-2011, 01:10 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