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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Rapid input Via datasheet | RudyR_Seattle | General Discussion | 4 | January 31st, 2005 01:33 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06: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 |