How to make Excel or Access searchable?

[Jolly]

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.

[Island1]

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

[Island1]

There are much cheaper options, too, but they'll be more difficult to implement, at least to give half the dazzle you could give with Reporting Services very easily. Among other options: MS Access has it's own reporting, and you can front the MS Access dB with a Microsoft Excel spreadsheet or pivot table, but it's ugly (IMHO) and not near as consumer friendly (a real dog for searching, etc.).

If you have access to MSSQL Server / RS, then this is a really grreat way to impress ... if not, let's discuss your options ...

Bill

[Jolly]

I am open to anything that will work! Creating the db is not the problem for me. I have basic skills that area, and the information I am using is not complex. (Date|Name|Gender|Charge|Correctional facility|Sentence)

I would eventually like to tackle other ledgers on the Prison, but that can wait until I have the needed skills!

My conundrum lies in the transition to making the db a tool that can used without having to know special terms. Is there a book you can recommend, an article(s)?

But to start... I need an Access db with my information and then get a reporting service to translate the querys and analyze the info in the db.. that is correct? What reporting service/writer should I look at?

I see that you appreciate the importance of history, thank you so much for your help. If I can get this working, I think that it could be a wonderful asset to the archives, not only for these specific ledgers. You are doing the community a service!

~MJ

[Island1]

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

[MAK]

If you are person who likes to read books, then i would recommend 'Professional SQL Server 2005 reporting services' - Wrox publication.

[MAK]

If you like to read online articles and tutorials then you can follow this link.

http://www.databasejournal.com/article.php/1459531

[Island1]

I was wondering what happened with this one ... it was quite exciting and, as I said, I want to help you accomplish your objectives, having done similar stuff myself in free / "not-for"profit" time ...

Let us hear from you!

Bill