• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Transaction tracking - Access or Excel?

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.
3 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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>

Murphy1992Author Commented:
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.

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Murphy1992Author Commented:
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.
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.

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 : http://www.zoho.com/crm/zohocrm-pricing.html
Murphy1992Author Commented:
Thanks very much for everyones help!!  Greatly appreciate
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now