How to make Excel or Access searchable?
Hi everyone! Complete n00b here... patience is a virtue! :)
I'm considering a project for my history class that I dreamt up last night, but I don't quite know how to make it a reality. If anyone here has any suggestions, I would certainly appreciate it.
I'm currently researching some ledgers from the Parish Prison from 1853. They are super cool, but they are falling apart. My initial idea was to import the information from the ledgers into an Open Office Spreadsheet, Excel or Access (I have all) so the numbers could be easily accessed, and the ledgers wouldn't have to be touched by researchers, thus helping them live much longer.
While it is a good, practical idea, I don't know if a static database will produce much of a "wow factor" from my professor or my peers. Then I came up with the idea of making it searchable. For example: The researcher encounters a search type engine thing and can type in "assault" and all the people arrested for assault come up as results.
My question is this: How do I turn my database it into something user friendly that can be used like a search engine? Also, which db program would be best, Excel, Access or OpOf? Do I need a special program on the library's computer, or do they need to host something off of their website? Now I'm getting into a region I don't know too well.... so any and all suggestions on how to do this the easiest way, with the least amount (or none) of script writing would be awesome.
Plus I'll thank you in my presentation. I swear.
Would You Consider Reporting Services?
If not, perhaps another similar report writer? This would be the easiest way to hit an Access DB (even a spread sheet - but I'd advise going to a relational dB once you got the design down). You could create myriad reports on the data that allowed parameterization - and hence wonderful search capabilities.
I've done this with everything from geneolgy to cemetary listings, etc.. (I'm a BI Architect as a real job - this is what I often do for a hobby!) I'd really like to help you see the potential here, especially for the kind of data you're trying to make consumer friendly. And the best news is that if you get a good design in the Access DB, you can always upsize it to MSSQL Server once you need a larger foundation (this would, of course, be the ideal design environment, but I don't know if you're open to that ...).
Anyway, let's talk. You can call me if we need more time and depth, or just ask questions here in the forum. I'd love to help you realize your vision, here - and I guarantee you're going to be surprised as to how much more you can deliver than you have even contemplated.
Do I sound like an evangelist?
Bill
General Things to Consider ...
Thanks for the rapid responses! You're right: I do, indeed, appreciate the importance of history.
I'm not sure I can offer a single book or article as guidance. The issue here, as in a billion other pursuits, is that there is no one guide to exactly what you are attempting, from every perspective. Sure, there are lots of books on each element of data, database design, extraction, transformation, loading, and, most to your immediate point, reporting.
But the ideas here are not complex. You say your " ...conundrum lies in the transition to making the db a tool that can used without having to know special terms." The need will best be met through a presentation layer, such as a search application or other such tool. Sure, you can write one (myriad choices available, etc.), but why limit yourself to searches, per se, when you can write reports that can be just as easily used for searching - and much easier to use in implementing flexible solutions that can be changed on the fly without programing, etc.?
To the point: if you can populate the data fields (sound like you know the ropes there), then you can write a report on that data source that lets consumers select among parameterized criteria you can offer - such things as Date|Name|Gender|Charge|Correctional facility|Sentence (and even better, things such as "show me the females with theft-related sentences that were incarcerated between January 1, 1886 and 1895 at facilities in these three locations," and such.) If you plan out the data as to "possible uses" without closing any doors, you can always make a report pull anyting you want that resides therein - and you can then parameterize those criteria to allow consumers to make selections at runtime - all "out of the box," and with parameter picklist selections consisting of "English" names, regardless of the names of the underlying data elements, within a decent report writer.
Let me know how I can help further ...
Bill
