Transaction tracking - Access or Excel?

Posted on 2012-08-22
Medium Priority
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 85
ID: 38319858
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 58
ID: 38319961
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

ID: 38319993
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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 38320030
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

ID: 38320104
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

ScriptAddict earned 400 total points
ID: 38320808
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

vasto earned 400 total points
ID: 38329625
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

Author Comment

ID: 38333929
Thanks very much for everyones help!!  Greatly appreciate

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 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