What method to use for sales/inventory database with high volume formulas?

[skyxliner]

I need some ideas on how to put this system together, Please let me know which method is best to achieve the end result I want.


Data I Have:
- In-store daily sales log
- Online daily sales log
- Inventory quantity

Analysis/Actions I want:
- Sales log entry auto deducts from inventory quantity
- Average, min, max price for each item
- Suggested sales price when entering sales entry
- Statistics of sales history

Methods:
1. Excel Using SumProduct Formula (High amount of formulas result in 3-5 min save/refresh times)
2. Excel Using PivotTable (Better than sumproduct but still taking 1-2 min)
3. Excel Using Macro
4. Excel & Access Data Linking
5. Whole Thing Running In Access
6. Other


Which method will optimize a high volume of formulas that need to be calculated?

[Ron Coderre]

Since you want to impact inventory levels...
If it was me, I'd build it in Access.

[skyxliner]

I just read on another thread that pivot tables do not refresh automatically so that crosses number 2 off the table.

I was siding toward access but do you think everything in access database, or having excel be sales entry that LINKS to an access database to do the number crunching?

I have to learn access and macros from scratch so if i'm going to learn something I want to make sure its going to work haha.

[Ron Coderre]

You're new to this forum so I'll just ask you to please read the forum rules...especially the part about cross-posting to other forums. Immediately after responding to you in this forum, I visited another popular forum and found that you'd posted the same question there.

Here's the link to that other forum:
http://www.mrexcel.com/forum/showthr...41252#poststop

(I already put a link to this forum in the other forum)

[Ron Coderre]

While learning Excel VBA and learning MS Access are both noble pursuits, you'll get the most benefit from learning what you need to know to accomplish your goals. In this case, I'd say that learning MS Access would be most valuable.

[skyxliner]

Does excel VBA relieve the excel file from massive calculations? I know a bit of programming and I know you can use a loop calculation, but i'm thinking it might still take a long time to save and refresh.

Access I know can do everything I need AND more. I basically would prefer data entry in excel and if there is someway to offset the calculation automated on the side somehow...

[Ron Coderre]

While Excel *can* be an effective front-end to an Access back-end, you'll put a fair amount of effort into programming Excel to do what Access does natively (via forms).
More often, Excel is used to report Access data in a format that users can play with (for budgetting, forecasting, what-if analysis, etc).