Access or Excel?

[Fyresparxx]

I've never used Access. I have an excel document that I use to track inventory value for my small business. I'm trying to decide if I should convert to Access. Currently, I search for a part number and overwrite our cost field for that part with the latest cost. Excel then calculates what our sell price for that item should be. For some vendors, I enter the cost of one item and convert to the price of that item, but for others, I enter our cost for 100 lbs of an item and convert to the cost for one of those items. I need the document to calculate both our sell price as well as our inventory value at the end of the year, where I input a quantity for each item, and it calculates the value based on our most recent cost. Each part might have more than one part number, depending on which vendor we bought from, but no part number ever overlaps with multiple items.

Current Excel example:
QTY | Description | Primary Vendor | MPN#1 | MPN#2 | MPN#3 | Sell Price | Current Cost | Unit of Measure | Weight | Size

Someone told me that with Access I might be able to update items as a list instead of individually per line:


Date | Part Number | Cost
(Or add new items in a similar way)
And that the list would put the data in the correct tables of Access?

And that I would be able to search a part number or name and see the cost history of that item as:
| Date | Cost |

But I need to calculate our sell price based on the most recent cost. Not all items are calculated the same way. Sometimes I convert cost per pound into price per foot, or cost per package to price per item. Some items have a higher markup than others. Some items don't get calculated at all, because the vendor sets the sell price. I enter this value into my accounting software.

Would I be better off using Access or sticking to Excel?

[orange]

If you are thinking of moving to Access, then spend some time researching database concepts. Access is NOT Excel and is built on a different object model. Here is a link to various database planning and concepts info.
Good luck with your decision and project.

[Fyresparxx]

I came here based on this article: https://support.office.com/en-us/art...2-e825227fcaea

I'm essentially using Excel to maintain a database. I'm not using the visual and analytical features that Excel is good at. My data is relational, the way I understand it. One vendor has many parts, and one part can come from multiple vendors, so it sounds like I should be using Access. But I use many different formulas to calculate cost to price, and I don't know how well Access can do that because, as you say, it isn't Excel. I don't want to spend a bunch of time learning how to plan my database, only to find that it won't work for everything I need it to that my Excel spreadsheet can already do. I just want to know if Access can do what I already do, but more efficiently, since that's what it's designed for.

[Micron]

FWIW, I'd say if you need to do more than basic charting (I personally think Access charts are abysmal) or you need complex formulae for things such as statistics, then you may not be able to totally dispense with Excel, if at all. Otherwise, Excel can't match Access' ability to mix/match/filter data (and do basic aggregate functions). The example you posted is how data is organized in Excel most of the time, but it's a poor model to copy when it comes to relational db's. The trick with relational db's is to understand that tables are entities and fields are attributes when it comes to grasping normalization. You ignore normalization concepts at your own peril.

One vendor has many parts, and one part can come from multiple vendors, so it sounds like I should be using Access

Definitely could help. Whether or not Access can perform the calculations (they seem basic enough) will depend on you creating a proper db schema; e.g. you store the latest price in a table (you don't usually update it) and discounts per unit are stored elsewhere. Forms handle the price, weight, etc. calculations. Their output as an Access report far exceeds what you can do in Excel IMHO.

If you are not impeded by your current method, you might want to consider staying put as Access has a bit of a steep learning curve as Orange was alluding to. M$ has made it easier to do so with the later versions of Access - but not necessarily to do it well.

[Fyresparxx]

If you are not impeded by your current method, you might want to consider staying put as Access has a bit of a steep learning curve as Orange was alluding to. M$ has made it easier to do so with the later versions of Access - but not necessarily to do it well.

I don't do anything complex in this particular spreadsheet, just some very basic algebra to convert cost per weight to price per length, or even more basic multiplication to apply a simple markup. The only complexity comes from the fact that the entire sheet isn't as simple as "Column A * 2 = Column B." I'm capable of making the spreadsheet work, as long as I don't care about keeping a list of past price points. It would just be nice to not spend so much time updating it every day, line by line.

Thanks for the information. It sounds like I should look into learning Access between projects, but not expect to be making the swap too quickly. I want to get it right, for sure. Thanks again!

[Micron]

here's a canned list response I often provide for starters

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html