Results 1 to 4 of 4
  1. #1
    camiloeslu is offline Novice
    Windows Vista Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    5

    Complex spreadsheet in excel to Access migration

    Im having a real problem at work that could be solved with the next example.

    My paint company sells 3 different paint products cars manufacturers and importers around the world. Note: Red, blue and green paints costs $10, $20 and $35 USD per hour respectively.



    I’ve created a spreadsheet in excel that keeps track of my customers history, allowing me to see their status (active, interested, inactive) and last comments regarding the last contact date (see document attached). Note: Active means working on the project, interested means negotiating and Inactive means nothing.

    Considering that my list has increased with too many contacts and that the complex formulas in my spreadsheet is making my document too slow, I’ve decided to migrate to Access, considering its robust nature.

    I am trying to export the information to Access without losing info. My problem is that my excel list has too many columns, considering the several dates build initially to keep the history of my clients. I need some advice on to how to migrate my spreadsheet info while optimizing without losing any info.

    Note: It is worth mentioning that I use my current spreadsheet to see my active clientes and interested clients in dynamic tables in two separate tabs(nos in the excel included).

    Ill appreacite very much your valuable help


    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, this is just a starting point. (just tables/relationships)

    Just remember Excel and Access are two very different models.

    I see I missed the ZipCode field in tblCustomers.

    The table "tblComments" is for the Excel column that have the dates (light blue header)

    I think you might need another table for order details. I just used your Word doc and the SS to create these tables.

    Manually add data to see if the structure holds the data you want.
    Be aware that calculations like "Contact" in your SS column and total price are not stored but should be calculated in queries.

    This is what I think you should begin with:
    Click image for larger version. 

Name:	Paint.jpg 
Views:	18 
Size:	32.3 KB 
ID:	26240
    Attached Files Attached Files

  3. #3
    camiloeslu is offline Novice
    Windows Vista Access 2013 32bit
    Join Date
    Oct 2016
    Posts
    5
    Thanks for your help

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ssnafu's given you an example of what to do but I don't see in this spreadsheet any order/job information, it just appears to be a list of contacts and their status and dates of contact with each client.

    If that's all this is doing you might be able to run some code to parse out all your information and create your tables for you, realistically you are tracking every day of the calendar year and most of that is empty (at least in this example), in a database structure you only want to record events that actually occur so keep that in mind. This spreadsheet also would rely on the COMPANY being spelled identically for each instance of the company for instance Corleone and Corleone Inc and Corleone Inc. would be registered as three distinct (and different) companies.

    I think I would do things in this order:

    1. Extract the company names on your spreadsheet, make sure you have homogeneous company names *BEFORE* you start importing into Access.
    2. Do the same for your TYPE column (Importer, Manufacturer, and other options are all spelled identically)
    3. Do the same for SUBTYPE
    4. Do the same for POSITION (notice you have 'importt manager' and 'imports manager' in your list these would be considered NON IDENTICAL and should be fixed before trying to import any data.

    Once you have homogenized your data you should be able to import it all using code to dump it into the appropriate table. I do not see a good way to do this other than with code, or grinding through data entry to re-create it in a properly normalized database.

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

Similar Threads

  1. Format an Excel spreadsheet from Access
    By crowegreg in forum Programming
    Replies: 4
    Last Post: 12-23-2013, 07:12 PM
  2. Replies: 1
    Last Post: 11-26-2012, 12:35 PM
  3. Creating excel spreadsheet from access vba
    By nyneave in forum Programming
    Replies: 1
    Last Post: 10-12-2012, 09:59 AM
  4. Open Excel spreadsheet in Access
    By carlyd in forum Forms
    Replies: 1
    Last Post: 02-17-2012, 01:09 PM
  5. Email Excel Spreadsheet from Access
    By Nancy in forum Access
    Replies: 2
    Last Post: 11-09-2010, 02:37 PM

Tags for this Thread

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