Data entry and processing - MS Access okay?

Hi,

Problem statement: We have some amount of data stored in various excel
sheets and we generate a few reports from these data. We are in the
process of consolidating all data to reside in a single repository.
Currently, it is semi-automated i.e. we are using MS Excel for data
entry, consolidation and report generation (i.e. using formulas). We
require a tool that will provide:

a) A web-based interface for data entry (i.e. remote,
location-independent information access/updates)
b) Reports on a monthly basis (Simple dashboard type of reports with
simple data processing rules)
c) Multi-user support (Say 15 - 20 concurrent users)
d) Data backup and recovery support (The data volume is not huge but
the data is sensitive and critical)

Is MS Access a good choice to solve this problem? We are also
considering MS Excel + Sharepoint portal solution. An open source
third-party solution should also be fine.

I would like to know expert comments from this group regarding the
directions we can take to solve this problem. Please help!

re: Data entry and processing - MS Access okay?

You should look at this book, Microsoft Data Analysis, by Michael
Alexander, very recently published, it talks about how Access can
usefully replace Excel for data analysis and export for even more
sophisticated Excel analysis.

ISBN: 978-0-7645-9978-1
www.wiley.com/go/accessdataanalysis

There is a TON of tools out there to do all that you want to do...it
will take you a few months to sort out your options.

You will see differences of opinion on how much Access can be used in a
"client-server" setup like you want with 15-20 people.

SharePoint *Services* is free, SharePoint *Portal* is quite expensive.

Access can do "web" front-ends or you can use an ASP.NET 1.x or 2.x
front end to the data, but Access can do much richer forms and reports
than ASP.NET can without buying a lot of 3rd-party extras for ASP.NET.

For example, Access has subforms and a NotInList event, those don't
exist in web forms to the best of my knowledge...

It really depends how complicated the data entry has to be, how much
validation, calculating, etc. whether you use Access front ends or web
front ends.

Reports are very easy in Access, once you have the underlying queries
properly set up.

I hope this gave you some food for thought, I'm going through something
similar because I expect to have to upsize some small departmental DBs
to SQL Server and at the same time combine them all together!!!

Thank you, Tom

re: Data entry and processing - MS Access okay?

Yes, it did give me good food for thought. Thanks Tom!

Also, I would like to share an interesting link that in this regard.
Hope it would be useful to someone in some way or the other. :)

http://www.exceluser.com/catalog/landdash3.htm

re: Data entry and processing - MS Access okay?

Yes, it did give me good food for thought. Thanks Tom!

Also, I would like to share an interesting link in this regard:

http://www.exceluser.com/catalog/landdash3.htm

re: Data entry and processing - MS Access okay?

I think you should use Access, since you are not dealing with loads of data, Access can well handle it. if you were to use loads of data i would suggest to go for better databases such as Oracle, SQLserver. Although the security is more powerful in SQLserver and Oracle, Access is not bad for limited data management. Access will be able to easily support 15-20 users at a time.
What i have noticed with Access, is that it is worst when it comes to calculations. I had a business data where loads of mathematics was involved at the same time reports needed to be produced of this calculations. What i did was did all my maths in excel, linked/imported excel worksheets in Access then made all my different queries, forms and fabulous reports. Linking excel and Access tables has real time changes, one doesnt have to do "update link" thing as you have to in Excel and Word linkage.

The bottom line, Access is great for data entry, error free typing, friendly user interface, reports printing, etc and Excel is best when it comes to maths calculations.

re: Data entry and processing - MS Access okay?

Access, even though it has "Data Access Pages" (DAP), is not a good tool for
creating a web-based interface. You can, however, use .asp web pages to
access a Jet database engine database (aka, loosely, an "Access database")
using ADO, or the more current ASP.NET, using ADO.NET.

DAPs are only useful, in general, on an Intranet and, even there, are
limited.

You can link or import spreadsheet data into Access/Jet, provided it is in
row and column format, and not just data and formulae randomly sprinkled
about the worksheet.

Larry Linson
Microsoft Access MVP

"SRAM" <sudha.rams@gmail.com> wrote in message
news:1147163118.254259.13200@g10g2000cwb.googlegro ups.com...[color=blue]
> Hi,
>
> Problem statement: We have some amount of data stored in various excel
> sheets and we generate a few reports from these data. We are in the
> process of consolidating all data to reside in a single repository.
> Currently, it is semi-automated i.e. we are using MS Excel for data
> entry, consolidation and report generation (i.e. using formulas). We
> require a tool that will provide:
>
> a) A web-based interface for data entry (i.e. remote,
> location-independent information access/updates)
> b) Reports on a monthly basis (Simple dashboard type of reports with
> simple data processing rules)
> c) Multi-user support (Say 15 - 20 concurrent users)
> d) Data backup and recovery support (The data volume is not huge but
> the data is sensitive and critical)
>
> Is MS Access a good choice to solve this problem? We are also
> considering MS Excel + Sharepoint portal solution. An open source
> third-party solution should also be fine.
>
> I would like to know expert comments from this group regarding the
> directions we can take to solve this problem. Please help!
>[/color]