Template for Legal File Library

[sduffield2]

I've been tasked with organizing legal files for a corporate office. I
believe that Access should provide me with the best way to organize, and
ulitmately display the data. Still, try as I might, I can't get a setup that
I like. If you have suggestions to help me get control of this, I'd greatly
appreciate it.

1. The original file list is in an Excel file. Field1 is the Name of the file
2. Many of the files have files within files, in the Excel file each
separate file is listed horizontally (one file has 21 files in it).
3. Once the list is tranferred alphabetically from Excel to Access, I'd
like to re-code all the files based on their legal categorization.

It looks like it should be easy enough so I'm not sure where my problem is.
Should I make a separate database for each file and then link it up to a
Master File list (it seems like so much extra work)? Is there a way to link
cells to databases or other lists where I can input the extra data?

Anyway, thanks for your thoughts!

 

[John Nurick]

Hi,

The best approach depends on whether there's a fundamental difference
between (a) the "top level" files and (b) files that are contained
within other files.

If they're basically the same apart from the fact that each (b) file has
a "container" or "parent" while (a) files don't, then you probably need
one main table
tblFiles
FileName (or FileID) - primary key
Parent - foreign key into tblFiles.FileName
other fields about this file

So one row in the Excel file containing say one "top level" file and 12
others will translate into 13 records in tblFiles, one for the top level
file, with a Null value in its Parent field, and the rest forthe
"contained" files, each with the name or ID of the parent file in its
Parent field.

To move the data from Excel to the new structure, import or link the
Excel sheet as an Access table, and then run a series of queries to
append records to tblFiles. First, one for the top level files (i.e.
picking up just the columns in the Excel data that refer to the first
file in the row). After you've imported this, modify the query so it
gets the data for the first "contained" file in each row (picking up the
columns that refer to it plus the FileName or FileID of the parent
file), and append that. Then carry on for the second, third ...
contained file in each row ... up to the 21st.

If "top level" files and "contained" files are quite different beasts,
they should go in different tables - but there'll still be a "parent"
field in the lower-level table serving as a key to the name or ID of the
top-level file that "contains" the lower-level file.

Either way, it's probably appropriate to use a form to display the "top
level" files (or rather data about them), with a continuous subform to
display the files they contain.

 

[sduffield2]

Thank you John,

That was a very complete and thorough coverage of the issues I'm dealing with.

Thanks for the advice!

In a situation where I have given each "parent" and "contained" files unique
IDs, do you see me trying to build relationships between 3 tables?:

- IDtbl where the unique IDs and file names are all kept
- ParentFiletbl where all "top level" files are named and given IDs by Access
- SubFieldtbl where all "contained" files are named and given IDs by Access

I can visualize the relationships, but I guess I wonder if the relationships
between a parent and a subfile will become hard to update or search after
time. Would subdatasheets help? Is there a better way to normalize the data
that what I've come up with?

Thank you, again, for your thoughts.

 

[John Nurick]

Really, the key question is still whether the "parent" and "contained"
files are basically the same thing, except that some of them have
parents and some don't, or whether they are different things.

If they're basically the same, you need just *one* table (one main
table, at any rate, most databases end up with various auxiliary lookup
tables). This table will have one record for each file, with fields like
this:

FileID (autonumber, primary key)
FileName (text)
ParentID (Long, foreign key to FileID)
other fields

For a "contained" file, its ParentID field must contain the FileID of
its parent file; if it doesn't have a parent, the ParentID field must be
Null. There's a 1-to-many relationship between FileID and ParentID; to
create this you have to put two copies of the table in the Relationships
window.

If "parents" and "contained" are fundamentally different, they should
probably go in separate tables, one for parents

tblParents
ParentID (autonumber, primary key)
FileName
other fields

and one for the rest

tblContainedFiles
FileID (autonumber, primary key)
FileName
ParentID (Long, Foreign key into tblParents)
other fields

From your descriptions so far, I get the impression that "parents" are
just files that happen to contain other files, and that one table is all
you need. But either way, I don't see where you'd need a third table.

Whether you have one or two tables, the best and simplest way to display
the data is to use a form to display the parents and a subform to
display the contained files. (Datasheets are rarely the best solution to
a user interface problem, and subdatasheets almost never. I have never
needed to use them.)

With a single table containing both parent and contained files, the form
needs to display only the parents; do this by making its data source a
query which returns all fields from the table for all records where
ParentID IS NULL. If you set up the subform with FileID as the master
link field and ParentID as the child link field, it will automatically
display the contained records for each parent displayed in the main
form.

(With two tables, the main form is bound to tblParents and the subform
to tblContainedFiles, linked on ParentID).