Best way to create a filterable database containing hundreds of internal documents?

[belarin]

Hi all.



This is a bit of an odd request that may require some background to get the best advice so...


I work in a nursery and over the years have gathered a rather large amount of websites, links, books and sources of activities, games, rhymes and learning material. So much so that it is getting harder to find the ones I am looking for at the time I need them. For a little while now I've been thinking about making some kind of database that I can store them all in so that they are all in one place and I can easily filter and search for what I need.

What I need is a system that will allow me to store hundreds of items, each activity/game etc. May be a page or 2 long and possibly containing images/printable pages. It will also need to be fairly easy to add more to the list as I discover them and I would prefer if this was all self contained rather than having 1000 documents that are linked into the database and need to be present. I then need to be able to search for or filter all of the stored items with a number of "tags" (i.e. math games or sensory activities) and then display and/or print which ever is selected. It would also be nice if I could choose a filter and export all items of that type into a word document that I can print as a kind of book which the other staff can use as a reference.

I see it as a table with fields for the name and the tags with each item in that table linking to a page (form?) with the instructions/information laid out all in the background and then a main "front page" form containing the filters/search option to find and display the items desired.

My problem is I'm not sure if this is all possible to do just in access and if it is I have absolutely no idea where to begin, it has been a very long time since I used access and I only ever played around with it I've not learned how to use it on a project of this scale.

Any advice or input on how to go about achieving this would be greatly appreciated.

[Micron]

Don't completely understand the goal and some of the terms used. "Stored items" could mean anything. URL's to websites? Documents? Web pages (forget that!)? Export "items"?
Basically, storing documents or files of any type in Access tables is a recipe for db bloat and/or corruption in short order. A back end db type like SqlServer, MySql, etc. is a better choice for such objects, but perhaps not a good choice for a pure novice.

Typically, such files are kept in system folders with pointers (file paths) stored in Access tables, and the files are served up by the programs registered for those file types. I think you'd have to elaborate clearly on exactly what the goal is. Simply storing url's doesn't sound like it would achieve the goal, especially since you can't be sure of their permanence. To store actual web pages would require you to capture a lot of html info in folders, so that can't be it either.

[belarin]

Thanks for the response.

For the stored items an example a fairly well known activity is elephant toothpaste which makes mounds of frothy foam pour out of a bottle, so the item to be stored would be some text that I have written giving a brief description, ingredients, the method to do it and maybe a few bullet points describing what the children may learn from it. Another could simply be a paper airplane activity with a printable sheet to fold along the lines and text showing how to explain the basic principles of flight to a young audience. does that help?

I can see how having all that text inside the database would bloat it, I would just prefer not to have to create a new word document for every single activity/game as that would very quickly reach 1000+ .doc files which would have to be present and in the correct location for the database to find and link to which would make it a huge pain in the neck to move/maintain, it may even loose formatting if the computer it's transferred to uses a different document app.

The goal is basically to have one file that I can take to any computer open it, press a button labelled say literacy and have a list returned of all the things tagged as such I can then click one of those to view/print that particular activity. The more I look though the more I get the feeling that I'll have to have separate documents and then just have them linked into a table where they can be tagged for filtering.

[Micron]

does that help

A bit, but still fuzzy.

so the item to be stored would be some text

My definition of text is all of the printable characters. How they are presented is another thing - electronic document (including tv) or on paper are the only methods I can think of at the moment. The introduction of computers has probably caused the old fashioned "text" reference to no longer solely apply to books and the like. So text in a database isn't an issue as you suggest. It's the form of data that uses the least amount of space when compared to actual files of any type.

What you seem to want reminds me of a recipe database since the desire seems to involve several classifications of types and sub types for all sorts of things (like activity, age group, materials etc.). If you already have lots of printed or hand written ("text") information, consider scanning into pdf's so you don't have to recreate them as .txt or .doc files. The paths to these files can be managed and served up by Access. For others, you can enter information into tables and retrieve these as reports - same as has been done for meal recipes, I'm sure. Hope that provides food for thought

Warning: if you are very new to the concept of database design, do yourself a favour and read up on these very important concepts. They can save you a ton of headaches down the road.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

[belarin]

Thank you very much, your suggestion of a recipe database is almost spot on what I had in mind, I hadn't considered it from that angle and after a little searching I found a couple of sites describing the process of creating a recipe database and have learned enough from that to be able to build a (pretty basic) setup. It will still need a lot of tweaking and work to do everything I would like it to but already does most of what I actually need it to.

I shall continue playing around to see what I can figure out and make it do but will probably be back with more specific questions as needs arise.

Thanks again.