Results 1 to 13 of 13
  1. #1
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7

    Large number of files from excel to access

    Revenue on books for 5/5/2016
    Site 6/1/2016
    6/2/2016
    6/3/2016
    6/4/2016
    30 1,200 1340 1290 1100
    25 1265 1567 1800 2110
    56 1300 1400 1350 1375

    I have a folder with lots of what I call a "snapshots" in excel format. The snapshot is an excel file that is saved showing what the revenue on the books for a particular day. So in the above table you can see an example of a snapshot taken on May 5th, 2016 that shows how much revenue is already scheduled for 6/1 - 6/4. This allows us to see day to day pickup in revenue. The problem is there are now 2300 of these excel files.

    When I want to analyze these my current solution is to use Excel and VBA to automate grabbing the files and inputting the data into my Excel Model. The issue that is happening now is that the excel file has over 700,000 Vlookup Match formulas and it is taking an extremely long amount of time to update the file when I want to compare two dates.

    I have not used Access in my current or previous role so I a not sure the best way to go about uploading 2300 files to an excel database. I can use VBA to fix the file format in anyway I need to just kind of looking for suggestions on how anyone would go about this project.



    Any advice is much appreciated.

    Thank you.

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Before you begin importing the data into Access, you will need to normalize your data. In your example this means changing your layout to

    Site Date Amount

    Once your data is normalized you could run VBA to import your data to an Access table and Append it. Alternatively, you could merge all your Excel files once they have been normalized and then import it to one table.

    Look at this link on db design.

    http://sbuweb.tcu.edu/bjones/20263/a...sdb_design.pdf

  3. #3
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    So with normalizing the data would the proper way to do it be as follows...

    Snapshot_Date | Site | Date | Rev


    for all 2300 snapshots in one table? I'm assuming I don't need a Unique key for this type of data?

  4. #4
    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
    Do you have a clear description, in plain English, of "your business issue/opportunity"?
    Access is not Excel - it is built on a different object model and relational data base concepts apply.
    Tables, primary and foreign keys, normalization, relationships are basic terms/concepts you should be familiar with in order to use Access(or any dbms) effectively.

  5. #5
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    My business issue is this...

    Currently, we have a field of managers who manage multiple sites all over the country. They need to be able to compare dates from last year with dates from this. They currently have the ability to do so on a single site level but when they want it grouped by Market, Region, etc it is not available to them. I was able to build a solution in excel that pulls 4 of the described "snapshots" in the first post into the model based on the users date inputs. The files are all uniformly named with the date so it is easy to reference and pull in using VBA. The issue is that once the two snap shots are pulled in I have to have another "Data" sheet that uses over 700,000 Vlookups to put "Normalize" the data and match up the date differences that the Manager is trying to compare so it can be put into the pivot table view.


    The 4 main snapshots are this year's date revenue scheduled, this year's date items sold scheduled, and then the same for last year so you can compare the two dates. I also have a site "sit details" file that list information about the site that I am assuming will be its on table but linked to the Snapshot Information through a one-to-many relationship so the user can also see details of the site.

    I want to format all of this data in an easily accessible database so the excel files aren't so large and the end user will not have to run VBA code through an excel file to pull files out of a shared drive in order to compare the two dates they want to look at. The fields a user needs to input right now in order for the model to know what information to pick are snapshot date for this year and snapshot date for last year. The biggest thing is they would like to see how they are pacing into holidays, events, etc. If an event occurred on May 29th this year and May 15th last year. There is a 14 day difference so if they were to use today's snapshot they would take a snapshot from 1 year and 14 days ago so they could compare the pace based on date differences.

    As you can see this would be easy if I just normalized the data and was able to put it into a database with the fields

    Snapshot_Date, Site, Date, Revenue, Sold,

    Hope this helps. Thank you

  6. #6
    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
    Date is a reserved word in Access. Suggest you use a prefix or suffix that makes sense to you. You mention Event but I don't see that in your suggested table.
    How many snapshots do you intend to keep/have access to?

  7. #7
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    I want all of 2015 and 2016 at minimum. ~500 snap shots. I also want to keep adding to the snap shots everyday a new one is created.

    Events could be another I guess. Most of the managers keep track of that on their own and have their own file with when events were in the past and when they are this year so I was just assuming they would know the dates they would want to pull rather than having the ability to select a certain event and it automatically compare. Obviously the latter would be ideal eventually but for now I'm assuming it'd be easier to just make the database the way I described rather than trying to compile all of the events from all of the managers.

  8. #8
    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
    No, it is usually easier to take the 30000ft level of your requirements and make a plan.
    Make sure the pieces are all clearly identified and related according to your business rules.
    You can simply leave a particular subject area with minimal detail, but you know you have accounted for that subject in your design. Data base design is not a spot to assume someone will do something or other as everyone expected. It just doesn't work like that. Build a model of your tables, add a few test records, make sure this sample/prototype meets your needs--adjust as necessary and test until it's working as you need it. THEN, add you data and make it pretty.

    Good luck.
    Last edited by orange; 05-09-2016 at 04:39 AM. Reason: spelling

  9. #9
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    Thanks. What would you suggest as a primary key in my table? I just don't really understand that aspect because there really isn't going to be a unique id in my table.

  10. #10
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    Am I correct about needing to make snapshot_date a field in my main table instead of having a table for each snapshot and then link them all with a relationship by site number in another table?

  11. #11
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You will need a date field in your table. Is the snapshot date the same as the date shown in your first post? If not, then I don't see any reason for a snapshot date unless you will be querying your table for that date.

  12. #12
    Tyler_Analyst is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    7
    So if you look at my table in first post it has a title "revenue on books for 5/5/2016". This date will need to be queried for because it lets the end user look at what revenue was scheduled for future dates at a certain point in time.

  13. #13
    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
    My guess is that we still do not understand your full intent for the database.
    Take a few of you records and tell us in English-- who will do What with the data? How often will they do this and When?
    Records in a relational database will be unique, otherwise do not start thinking database.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-15-2014, 12:49 PM
  2. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  3. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  4. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 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