Transaction tracking - Access or Excel?

Posted on 2012-08-22
Last Modified: 2012-08-26
Using MS Office 2010

We would like to track stock quantaties on hand and their movements.  We require only basic information e.g

At Product level
Product ID, Description, Product Category, Supplier, Qty, Cost $, Volume

At Transaction level
Product ID, Description, Supplier, Invoice, Invoice Date, Cost $, Qty, Volume

We are a consumption based business therefore transactions will always be (+) increases with 1 adjustment at the end of each month for product used.  (-)

At this stage we use a primitive product id drop down spreadsheet that prefills description, volume upon selection.  Reports are then done of this ever growing spreadsheet.  

Its floors are the inability to select the product via pre-typing the first iniital values.  It must be selected from a very long list.  It would be great for the user to simply enter initial opening values (one off) and then all data is simply entered in form template.  The user able to start to type product id and the options appear for the selection.  Selection then prefills description, volume and possibly last cost applied.

It would also be great to see stock holding of each product but this isnt a must as this can done via report.

Can anyone suggest if this is better completed in Excel or in Access and possible point to some templates or possible outlines of others developments of this basic need.
Question by:Murphy1992
    LVL 84
    So you have Transactions, and those Transactions are related to your Products?

    What do you do with the data once you have entered it? Do you perform any reporting, exporting, etc etc? Depending on how much functionality you already have in the spreadsheet, you may find that you have a big task ahead of you to move to Access.

    With that said, anytime you're dealing with relational data Access is a better "fit". Granted you can force Excel to manage relational data, but it wasn't really built with that in mind and so you have to sometimes have to jump through a lot of hoops to achieve your desired results.

    In your case, if all you're looking for is an auto-complete combo, then you could use one of the ActiveX combos, or I'm sure one of the Excel Experts could show you how to use a Data Validation list to do this. For my money, however, if you're already to the point of outgrowing Excel (and it sounds like you are) then you should give a hard look at Access or .NET.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Access and I believe there is still an inventory template when you do a create new database.

     Ask a few questions here though and I'm sure you'll get some answers<g>


    Author Comment

    In simple terms we need per product
     - enter an initial qty held, cost $, volume
     - for each purchase enter invoice id, supplier, qty, cost $

    To ease this data entry (& errors) we need a product selector that also add description and volume once selected.

    At the end of each month we should be able to total initial qty held + or minus data entry values for the period.

    I will trial Access template and post some additional questions later.

    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    That's very straight forward and one thing you should consider is simply buying one of the off the shelf accounting products, such as Quick Books, which will do that and a whole lot more.

    Not saying you can't do it on your own, but many of the accounting packages out there sell for less then $500.

     You'll most likely spend far more then that in getting this setup when all is said and done.


    Author Comment

    Thanks I agree.

    We already run a accounting package, unfortunately it doesnt have stock control nor can it be separated as it is used for quoting / job pricing.

    In saying that perhaps I can save alot of grief by simply applying auto complete combo box for product id as the balance I can report via Crystal.  Ugly for a basic requirement.

    Thanks for your assistance.
    LVL 11

    Accepted Solution

    Man, for what your doing I'd go with Access as well.  

    In about 3 hours you could have a nice switchboard database setup with reports, and anything else you need.  

    Now that Access allows distribution of the runtime without a developer license you could even set it up to use on end user machines without having to buy them an access license.

    Access 2010 runtime is allowed.  Dunno about others.

    I'm not sure but, if you base it off of a switchboard, you might not even need to do a custom ribbon to get it functional, but if you do it's not that hard and tutorials are  available online.

    LVL 18

    Assisted Solution

    You can try to find an online solution . This might cost something , but you will have a real solution with $0 development. Check for example :

    Author Comment

    Thanks very much for everyones help!!  Greatly appreciate

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now