Regarding Tables

[Ozmosis]

I am in a situation here where I have about 27 drawers of filed items that need to be indexed. Each drawer is unique to itself, in that it has its own type of documents, but they do in fact all have a relationship to eachother.

My question is, what is the best method for keeping these organized? 27 mini-tables, or 1 big table?

What is the best way to go about doing this, while still allowing a strongly searchable database to be possible?

Thanks in Advance

Ozmosis

 

[WildHare]

Depending on how many records you anticipate, I'd probably find it easier to use ONE table, with an appropriate descriptor that relates to the file-type.

This way, it's easy to add additional file types in the future, if need be - you don't need a whole new table.

Reporting, forms, and querying needs will probably be simpler as well.

Basically, I look at it like this : different tables are uses to store items of a distinctly different NATURE. Filed items are different ONLY in their 'type' perhaps - it could be that you've withheld important information that would lead me to believe otherwise, but I'm assuming you simply have different types of paper stuff - contracts, memos, agendas, specs, et cetera.

If you are filing things like airplane blueprints, dead butterflies, pre-columbian terra-cotta figurines, and swizzle sticks from around the world, then you would probably use a table for each thingy....

HTH

Jim

Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[gillianleec]

You might want to read up on table normalization.

Let's say you have a student that takes several classes at your school. You would want a student table with ID and demographics in it. Then you would want a second table, linked by ID (which would have to be unique) to track classes taken and relevant information. You might want a third table that has the specific class information in it so you don't have to enter this for each student who takes the class.

This, of course, is highly simplified. Some reading would really help you before you start designing. Draw a relationship diagram before you start as well.

 

[Golom]

I guess the real question is:

Does "drawer" merely provide location information (i.e. its data about where the document is) or does it describe the information (i.e. its a data type)?

If the former, (as WildHare is assuming) then the single table approach is appropriate ... "drawer" is just information about a document (i.e. where to find it.)

If the later, then each document in the drawer consists of a range of properties that collectively constitute its data type. You won't be able to directly store different documents (i.e. different data types) in a single table because different attributes will need to be saved for each document type. You could of course build a table with all possible attribute fields from all possible documents but you then have violated one of the normalization rules that says "... each field should be a property of the Key, the whole Key and nothing but the Key ..." A field that applies to documents in drawer 1 but not those in drawer 2 fails that test.


If you're not confused by all this
it just shows that you're not thinking clearly. (Anon)

 

[Ozmosis]

Here's the deal:

All of the files in these drawers have the same 5 fields.
Drawing Number, Description, Work Order, Grid, and Notes

All descriptions are unique, most likely, and there are multiple Grids that can be the same.

the Drawing Number, however, should be unique without question, as that is where the drawing is located.

Example Entrees

A drawing in drawer 18 is something like:
18-101 : Haxby Subdivision : 03-012 : 1757 : ALTA Land

A drawing in drawer 22 is something like:
22-167 : Turnagain Heights : 02-020AS : 1231 : Preliminary

Hope this helps.

 

[gillianleec]

You will want one table for all these documents since they are they will all have the same fields and they are not subsets of each other. This way, you can do quick searches on the individual fields like Grids or Description to either find a document or a group of documents.

Gillian

 

[Ozmosis]

here's a good one for ya...

All of the numbers in my primary key are unique (duh), BUT they are sets of them that have something in common..
example..

There are 167 drawers in drawer 18, name 18-001 thru 18-167.

Same goes with all other drawers.

How do I set up a query/form combo to allow a user to search by description in specific drawers, example, all 18-*** plats, and 19-*** plats..

Thanks in advance.