Access vs Excel

[garyswann]

Hi Everyone,

I'm sorry if something similar has been posted, I'm new to access (learning on my feet)

Background:
I need to monitor a teams productivity of work based on a predetermined (changeable) target. I've previously used a excel form to capture data which transfers to a separate master spread sheet which then populates figures in various pivot tables for a dashboard for management. With up to 14 ppl being in the capture spread sheet any once, it often corrupts and in doing a bit of research, it's a common issue with excel and "shared" files.

I found that Access seems to be a better resolution in reading and I've started to create a capture form for the team to use, (baring making sure fields can't be left blank etc) I use the data and export it to excel and update the dashboard with the captured data.

Issue:
I need some guidance when it comes to the dashboard, our systems aren't the best (windows 7 32bit with 4gb ram and under 3gb usable) it really struggles with the excel dashboard that I've created because of the various calculations that I use to calculate the %age productivity of a person based on volume completed divided by volume expected. (volume expected is calculated based on average of hours worked minus and time lost due to different activities such as breaks, meetings etc. for example 7.5 hour day minus 0.33 for breaks.)

I'd like to be able to have the data in a access database but I'm struggling with being able to add the excel calculations into the database, I'd like for the data to sit in the table, for the calculations to update automatically with the new data and then update another form that would be a new dashboard. (I hope that makes sense)

Long Term:
Long term I want to be able to port the data into our works SharePoint services so the submission form is web based via our intranet but then also the dashboard is also viewable and printable via the intranet as well, I'd like to get to a stage were I'm not spending an hour of my data exporting and updating data and then printing dashboard when I can set it up so it updates automatically and the management can choose the data they want to view, it updates pivots and graphs etc and I can focus on my actual work :-S

Thanks for viewing and if you've any questions I'll try and answer them as best I can.


Gary

[ranman256]

Access is the answer. The 'excel' calculation goes in the queries. You can either perform functions on fields to get your formula result there, or do it in a series of update queries on 'result' fields.
Like averages, sometimes you must run a 'sum' query, then run a query on the sum query to get complex averages.

[ssanfu]

I found that Access seems to be a better resolution in reading and I've started to create a capture form for the team to use, (baring making sure fields can't be left blank etc) I use the data and export it to excel and update the dashboard with the captured data.

Access would be a better choice with 14 people in it at the same time than Excel.

You should start by working through the tutorials at Roger's Access Library. Take the time to actually do the tutorials. Access tables are NOT excel spreadsheets and don't work the same - never have, never will. The main thing is to get your table structure correct before creating forms/queries/reports.

You will want to split the database into the front end (FE) and back end (BE). The BE only holds the tables, while the FE has the reports, queries, modules and forms.
The be should be on a commonly available location that everyone has read/write access.
EACH person will have a copy of the FE on their own computer linked to the BE.

AS far as the calculations, you can write UDFs (user defined functions) to do the calcs.

Long term: Be aware that Sharepoint does not support VBA.