John,

Jeff's advice is very good. Seeing it I remembered I had written
something on the subject a few months ago which helped the original
recipient, so I thought you might be interested. It is nowhere near
exhausting the subject, I would say it's much more like just touching
ground so you have an idea what you're looking for. Here it goes, for
all it's worth:

The idea behind the "entity - relationship" model is simple, model your
project as being made up of entities (having attributes) which have
clearly defined relationships between them. Think of a school: there are
students, teachers and classes - these are the entities involved. A
teacher teaches several classes; a one-to-many relationship. A student
takes several classes, and a class is taken by several students; a
many-to-many relationship. A student has attributes: name, sex, birth
date, address etc. A class has attributes: name, teacher etc. A teacher
has attributes: name, title, faculty, address etc. So, by now, you must
already be seeing these tables:

tblStudents
S_ID
S_Name
S_Address
S_Sex
S_BirthDate
etc.

tblTeachers
T_ID
T_Name
T_Title
T_Address
T_Faculty
etc

tblClasses
C_ID
C_Name
T_ID
etc

Notice that each table has a ID field, a unique identifier for each
record: the table's Primary Key. Now notice that tblClasses has a T_ID
field, which is a PK in another table; this is called a Foreign Key, and
is the "many" side of the one-to-many relationship between tblTeachers
(the "one" side) and tblClasses. Note: it is not necessary that the FK
in a table has the same name as the PK in the "one" side table, it just
helps understanding here.

What's not apparent so far, is the implementation of the many-to-many
relationship, right? For this we would need a separate table, let's call
it tblEnrollments. This would look something like:

tblEnrollments
E_ID
S_ID
C_ID
E_Year
E_Semester
E_Grade

Notice there are two Foreign Keys here. What we have done actually, is
introduce a new entity Enrollments, with a many-to-one relationship on
Students, and another many-to-one relationship on Classes; in other
words, we broke down our original many-to-many relationship (students to
classes) to two one-to-many ones. We did so because one-to-many can be
worked with, many-to-many cannot.

HTH,
Nikos

John Sawyer wrote:
Hi this is going to require quite a bit of explaining and I appreciate any
help in trying to aid me. I am relatively new to access, but I am having a
hard time thinking of an efficient way to set this database up, and have it
kept up-to-date without the aid of a third party "table organizer". Perhaps
my trouble is in not knowing how to efficiently describe my problem.

I need to input several fields of data. Part Numbers, dash numbers,
reference documents, sales order numbers, reference document history, having
read the document for the first time. The problem lies he the data can be
constantly repeated, at least between the part numbers, dash numbers, and
reference documents. Sales order numbers will be unique.

These dash numbers can repeat for any part number obviously because they
work like revisions. you can have part number 123-1, 123-2 or 222-1, 222-2,
222-3, and so forth. The other problem is that the documents can affect more
than one part. You could have 123-1 and 123-2 both being referenced in
document 1. The same reference document could also refer to 222-3. This part
has been worked out: I have the reports be reorganized when it is time to
view. The table stores multiple entries of the part numbers and dash numbers
so that there can be a unique document number for all that apply.

The fun part: I need to add in another table that will be imported. This
will provide a sales order (unique) that will reference a part and dash
number. I need to be able to pull up the reference documents (not too bad),
but I also need to keep tabs on that information. The reference documents
need to be noted in another table when they are read. If you read the
document for the first time it must be marked as such, and the second time it
will be marked as previously read. This data will probably need to be stored
in a separate table because it is dependent upon the sales order, part
number, and dash number.

The reference documents can be added continuously, which can complicate the
matters further. The only thing I can fathom when I attempt to design this is
to have a second dimension, where the reference documents can be listed (or
nested) within a given sales order number. I do not know how to accomplish
that with one table. I also do not know if Access can add new tables on the
fly, as I had considered that to keep track of every unique sales order (but
I do not know the limit size of the database yet).

If anyone can suggest anything, even a new way to design this, I would
greatly appreciate it.

Thank you