MS Access vs MS Excel question

Gator

Right,

to all you MS gurus out there. sorry for the ramble, if it doesn't make sense, tell, me, its confusing me!

I have been tasked with producing a dashboard in work which will be fed by a range of other excel files. My first thoughts were to standardise the dashboard so that the format would remain static and then reduce the amount of excel files feeding it to a minimum. I am now starting to think that maybe a interim DB may be the best solution i.e. get the user to directly input the data to access (do a bit of manipulation) then export the access data to a single excel file (the dashboard).

My first question is;

If I was to scrap the source excel files and make everyone fill out forms in access how easy would that be? i.e. how distributable is access, how feasible is it for some to open an access file and input their data? (sorry, but I rarely use access and if I had my way I would do all of this web based with PHP).

If I cant scrap the excel files what does the data export and import look like in the real world? The end game would be that someone opens the excel dashboard, it then looks to the access file and imports the most recent data automatically (the users will be inputting dates in a standard format so I can just look to today's date minus whatever ). How easy it would it be to import the data into access (once the tables are there)

Il set the scenario as it might make a bit more sense...

Say we have 3 spreadsheets which users are inputting data, one record per line with a date.

That data will be then uploaded to an access db were the data will be manipulated (averages, trends will be calculated and a lot of other stuff)

The data will be then exported to a spreadsheet automatically when someone opens the sheet (file names will remain constant)

What I would like to get to is that people are inputting the data in forms directly into access and I can then export this data (auto) into excel.

I would like get some opinions and feedback

cheers for reading

aidan_walsh

Moved from Software Design.

Gator

Yeah...this looks like the spot...right in there with all the hardware questions

Freddio

A solution Ive seen commercially is to have an access database on a network shared folder and then on client machines a local access database which acts as a database client connecting to the primary database via ODBC.

You could then build forms for data input on the client db and reports on the master db.

aidan_walsh

Gator wrote: »

Yeah...this looks like the spot...right in there with all the hardware questions

Plenty of software questions been asked and answered before, so here it stays.

Gator

Cheers Freddo,

I had a think about this and I am going to go ahead with that as an interim solution. In the meantime I am going to build a DB with web front end to show them what they can do and hopefull they will invest

BostonB

You started off taking about a dashboard but that seemed to change into a standard database that exports to Excel.

Whats the dashboard got to do with this?
Do they need to do more with it in excel or something?
What manipulation do they need to do in access?