MS Excel and Access - which is better for reports?

Question

Where I work, staff have just started to use a basic table in excel (1 October) to record sales which has about 10 columns (name, client, renewed, discount, paid etc). I record the data (total sold etc) every hour and email it to the manager. Each staff has the their own file on the network which they use constantly for that day (eg. John 08-10.xlsx; John 09-10.xlsx etc) and have been told to save the file after they complete a row with client data.

I can see the file (in read only mode) to update the report but I am sure there must be a way of doing an autoupdate of their worksheets in real time. I can link worksheets and workbooks to my main workbook but manually. Does anyone have suggestions on have to do this on Excel? Or would Access allow me to make a report which shows the sales total for that hour without the staff closing the file or constantly clicking save every few minutes?

Answer 1

I did some reports in Excel (with and without VBA) and in Access. Reports in Excel and Access have their own strong and weak sides. Without being more specific, I can't help you well.

If you go with the Access solution, you will have to use VBA and spend at least a few days setting things up. In that case you might also consider using VB6 and write stand-alone application - the code is almost exactly the same, but debugging is much better under VB6, than in VBA (I mean: debugging classes). And you might easily use version control system.

As for Excel:

If you rely on formulas, reports in Excel can reliably feed only on already opened workbooks. Open workbooks don't update their contents if other program try to write to it (add new lines to a table).

So for the update part, you need some sort of VBA automation, but it will be very small and simple.

Excel have formulas, and you may do miracles with them. Personally I find it easier and faster to work with formulas rather than with VB code. If you base your report on formulas you have the interactivity for free. To dynamically format charts, you must use VB(A).

Long reports based on formulas often take long time to calculate.

Answer 2

Please check the link. Hope it helps you in some way.

LINK
Conclusion from the linked article:

Excel makes it easy to generate custom output with very flexible formatting and annotations that you can add anywhere. The payoff with Microsoft Access is how databases simplify things over time. It may be overkill for one time analysis, but if the data and reports need to be maintained over time, spreadsheets often hit a wall. Most organizations have many "similar" spreadsheets that are tweaked slightly differently and rapidly become unmaintainable. A well designed Access database avoids that limitation. That said, both Access and Excel have their strengths and weaknesses.

A hybrid solution where data from an Access database is exported or copied to Excel often provides the best of both worlds. The data integrity of a database with its well defined and approved output in conjunction with Excel for ad hoc analysis lets you leverage the advantages of both. Using automated processes, the sharing of data can be very smooth.