Results 1 to 6 of 6
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    Should I Normalise my Tables or Not?

    Here's my dilemma.

    I have a stock market portfolio created in Excel with 1000-odd records with many columns containing duplicate data: Account, Strategy, Product, Exchange, Stock Code, Stock Name, etc etc.

    Creating a new portfolio manager in Access, I have two options: Create separate tables for each Excel column of data, normalising the duplicate data into separate tables and linking them to the main portfolio manager table with Combo Boxes to select the data for entering new records (looks great but a nightmare for importing to)... OR, create just one main table in Access allowing me to import directly all the data as it appears in Excel, allowing duplicate data to exist. But un-normalised.

    What's everyone's thoughts? I use Excel now because it's simply fast for formatting and manipulating data, But Access looks and operates more professionally. The image below is the main portfolio manager window I've created in Access so far, but no imported data exists yet. Thanks.
    Last edited by DigitalAdrenaline; 06-14-2019 at 07:33 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You would still have this table with all these columns but instead of repeating the descriptive text, would have a numeric key that links to a lookup table. If memory and db size is a concern then yes, redesign. Regardless, you should probably have lookup tables anyway to control what values are available to users. Just have to decide whether to save text or number key.

    Where are you getting data from?

    It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    All the records currently sit in Excel. I just didn't want to retype much of the data again into Access once the other tables are created.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can minimize retyping by runing UPDATE queries to add numeric key values.

    Create new lookup tables. Build a DISTINCT query to get unique values for a field. Copy/paste those into new table and allow Access to generate the autonumber ID (or run MAKE TABLE action). Create new field in the original table to UPDATE with the new key by joining on the common text fields. Delete the old text field. Do this for each field you want to convert.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks June, will look into that further. I think I've also solved my initial dilemma from watching an Access video. I can import my Excel table to a single new Access table and then run the Analyze function which will separate the columns into individual normalised, linked tables with combo boxes to the main table. Then I can just carry on adding new data to the tables as required. That function solves a massive importing headache. Cheers.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ah, okay, I've never investigated the Analyze tool, although think I have read about issues with it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  2. Best way to normalise.
    By tommywat in forum Access
    Replies: 4
    Last Post: 07-06-2015, 05:29 AM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Replies: 4
    Last Post: 11-22-2013, 11:20 AM

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