Any Excel/Access experts out there?

Daraius

Hey all. I'm fairly proficient with Microsoft Excel but I've never used Access before. I'm about to start a project to help me keep track of costs and contribution margins for all ingredients and products at my bakery.  

Specifically, I'm going to need to record costs per unit/pound/gram/etc for each ingredient, use those values to compute costs per batch/unit of each product, and use those values to determine contribution margins for each product sold in-store and to other vendors. Crucially, if the price of an ingredient or the selling price of a product changes, I want to be able to change just the relevant value and have all computations update accordingly.

I'm fairly sure I can do what I need using Excel, but would it be worthwhile to learn Access and build the tables there instead?

Ianir

There are a few questions you need to ask yourself before adopting a tool for any purpose:

* Will my old tool I actually know do the job well?
* Will this new tool make my life easier in the long run?
* Is it going to be worth the time investment of learning the new tool?

And tying into those,

* What is the purpose of the tools?

Excel is excellent for spreadsheets and computation amongst those. If you're working with simple POD (Plain Old Data) constructs that fit well into a spreadsheet, Excel is probably one of the best tools for a task.

Access is a RDBMS (albeit a non-ACID-compliant one), so where it shines is data lookups across complicated table sets and relations between tables. It is also far better in establishing a type security for your tables, ensuring you have at least potentially the right data in the right locations. If this sounds complicated, I'll just point out that some people have dedicated jobs doing administration on these with salaries in the $60-80k range. However, don't let that discourage you.

The learning curve on Access is far higher, but if you need the relational lookups or complicated computations, or higher external scripting accessibility, it's probably what you want. However, if you just have two tables that relate to each other and already know how to do it in that software, I'd say just use excel. The cost of time spent learning Access will be higher than any benefit until your data gets a bit more complicated.

tl;dr: Excel's probably the way to go here.

Kagato

Gotta agree with @Ianir here.  I have been using Excel for various tasks for a good 15 or so years, it can be a VERY powerful tool if you know how to use it.  You might need to look at taking an online class/webinar to learn the various tricks of how to get the most use out of it, but given what you want, I would strongly recommend it over Access.

Daraius

Well, that's promising since I know my way around Excel already. Thanks for the advice!

Havastus

Reading between the lines here, it sounds like everyone is encouraging you to stick with using Excel for your workload. For whatever it's worth, I agree that sticking with Excel is the right choice for your use-case.

Use would normally choose a Relational Database when you are trying to keep track of different types of data that are related in some way. For example, if you wanted to track, say, "rewards cards" members and what items they're more likely to purchase (and perhaps offering discounts on those items for rewards members, etc), you would have two sets of data: rewards card holders and your store inventory. Then you'd relate those two datasets with another table, which would track transactions from rewards members and the items they purchase.

Since you only want to track one set of data, using a single Excel sheet to do so works perfectly. You can divide up your data and compute all sorts of fun metrics!