A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Referencing continuously updated data



 
 
Thread Tools Display Modes
  #1  
Old January 11th, 2005, 02:11 PM
John Sawyer
external usenet poster
 
Posts: n/a
Default Referencing continuously updated data

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
  #2  
Old January 11th, 2005, 04:08 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

John

As a starting point, I'd suggest spending some time designing your table
structure and relationships. The topics you'd want to search this newsgroup
(tablesdbdesign), Access HELP, and Google.com on include:
normalization
relational database


I believe I've heard reference to a book titled something like: "Relational
Database Design for Mere Mortals".

If you'll turn off your computer and sit with paper/pencil, you can begin by
identifying "entities" (things about which you want to record data) and the
relationships among the entities.

For example, if we were talking about banking, you'd probably want
information about persons, about accounts, and about which person(s) were
connected to which account(s). That implies three tables.

What "entities" are you dealing with?

--
Good luck

Jeff Boyce
Access MVP

"John Sawyer" John wrote in message
...
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


  #3  
Old January 11th, 2005, 04:28 PM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rapid input Via datasheet RudyR_Seattle General Discussion 4 January 31st, 2005 02:33 AM
transpose john Using Forms 1 November 24th, 2004 07:16 PM
Is this possible with Excel Chart? q582gmzhi Charts and Charting 1 September 8th, 2004 03:33 AM
Countif with 2 or more data ranges in same column Doug Worksheet Functions 1 July 4th, 2004 08:57 AM
How to create graphs in a monthly report where the base data can change John Clarke Charts and Charting 3 June 25th, 2004 02:22 AM


All times are GMT +1. The time now is 06:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.