Building a database within excel

[hyperoptix]

Hello,

I'm looking for feedback on the best way to build a user friendly, but data rich environment. I work in the ophthamology field, and we are looking into ways to track a multitude of laboratory issues we experience. I'd like to create a database within excel (I do not have licensure to access Access) that has the ability to identify preset form fed issues by location, and measure frequency across time. There'd be roughly 40+ locations potentially contributing. I already know that they will NOT use this if it is not VERY straightforward. On the back end, I need the best way for management to assess the data I'm collecting.

I'm still in the infancy stages and really just want to collect ideas. Initially I'd wanted our IT department to put an HTML form on our intranet homepage, and have the data collected feed into excel via SQL somehow, but I don't know if this is as clean as I'm hoping to keep it to promote utilization.

Your suggestions are most appreciated.

[dflak]

First of all, you don't need an Access license for every person. You need one for development. Everyone else can use a free, runtime-only license. This is actually a good thing, because the only thing that a runtime-only license can do is run the forms, queries and reports you develop for them. They cannot develop (that is, break) what you developed for them.

If you must press Excel into duty as a database, the same rules apply: a well-designed table structure will save you a lot of headaches later on. Here is some help on doing that: https://www.excelforum.com/tips-and-...zing-data.html.

The problem is that Excel is very good at reading and analyzing data. It does a better job at this than any database because that is what it was designed to do.

Excel sucks at data entry. It's not designed to do that. So what you have to do is learn Excel Forms and program them to do the work that Access forms do almost automatically.

You need to develop code to add new data to a database, delete data from databases (and "connected" records) and find existing records and change them.

Good luck.

[ubludkisranye]

Excel is not a database engine and doesn't have key features which make a database work such as concurrent users, record locking, table relationships, so I think this will be frustrating.
If you've not used Access before then it's a bit of a learning curve, plus it also sounds as if you don't have much support from the either the users or your IT department, which I think you're going to need.

[frabulator]

I already know that they will NOT use this if it is not VERY straightforward.

From personal experience, this is the case with any application you develop, regardless what environment you are in. You can make an awesome product, but if your product takes more than 4 steps, you need to rethink it because it is too complicated.

What I would suggest is to not worry about the user interface this early on. Instead, focus on the main program.

have the data collected feed into excel via SQL somehow

What I have done before was create a PHP webservice on my website where my data is pulled from and dumped. The program checks the webservice, goes through the verification, then pulls the data needed. You could also do the same thing with a FTP upload/download (EDIT - which would probably be quicker and easier for your IT department to set up on the fly). Save an updated file locally and then compare/plot the new data.

Just a thought.