+ Reply to Thread
Results 1 to 3 of 3

Opinions on "database" that stores and accumulates SKU level data between Excel and Access

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Opinions on "database" that stores and accumulates SKU level data between Excel and Access

    Very open ended question so obviously interested in open ended approaches. Right now we create business forecasts from the bottoms up. We have 6 segments that have a number of SKUs, and we input a bunch of monthly P&L data (Sales, costs, etc) and those accumulate up to give us forecasts for that segment. A few problems right now:

    1) It's in 6 different files. Getting answers for the whole business is painful.
    2) Tough to put it in 1 file, its gets too large.

    The thought was to put Master Data in Access for us in Finance but keep the Excel files going for each of the managers. Have them complete their Excel file (aggregating from the bottom up), roll it up into Access for analysis, and we have Access and the 6 files talk back and forth.

    My broad question is: Has anyone ever done anything like this before-especially in a case where a line by line SKU data is concerned? Is there a better method?

    Again one file with all of those managers having their hands on it would result in a lot of complaining on the slowness and potential security issues since someone could inadvertently change someone else's numbers. Better for each to have their own and we guys in Finance do the backend work of having Access talk back in forth. Plus we want to do stuff like storing history (i.e. we wouldn't ever "edit" over an old entry. We would add a new entry and "turn off" the old entry so we'd have a record of changes...but that's for later.)

    Thanks and sorry for such an open ended question, just brainstorming here!!

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Opinions on "database" that stores and accumulates SKU level data between Excel and Ac

    I cringe when seeing such problems. I didn't use Access and Excel but used Oracle with a SQL front end. Each person that could input data and produce local reports had their own table(s) that they could read/write with a SQL front end suited to their purposes. I had access to all the tables with my own SQL front end and ability to directly access the tables without the front end to produce special reports as needed.

    This made doing administrative tasks with the data very easy and eliminated a duplication of effort for every satellite location.

    If Access has that ability, perhaps that is the way to go.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Opinions on "database" that stores and accumulates SKU level data between Excel and Ac

    Been a dream of mine to learn SQL - I need to make it happen. Yes, I'm stuck in Excel and Access for now. Perhaps if I do a good job I could get some funds for an upgrade.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 05-10-2014, 06:58 PM
  2. Setting "Current" access database and running macro from Excel
    By beat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2013, 12:07 PM
  3. Importing data from Access database to Excel. Dont want headers/"field names".
    By Treenigheden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2012, 08:35 AM
  4. [SOLVED] Access a worksheet level named range with Refers To like "=5".
    By Jeremy Gollehon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2006, 06:10 PM
  5. Using Access database to "populate" Excel Sheets
    By maacmaac in forum Excel General
    Replies: 1
    Last Post: 09-19-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1