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
|
|||
|
|||
4 table relationships
Hello all,
I'm trying to get a one to many relationship between four tables. Each table is embedded within the other. The process I wish to accomplish is to have my primary table with the primary key pull data from another table, the linked table will then pull data from another table linked to it. My problem is I need to be able to have indexed keys but multiple entries. Basically The first table can have only one work order, the next table can have many item numbers based on the work order, the last table wil have many layer numbers based on both one work order and the many item numbers. I have tried junction tables and using primary keys. Neither have worked for me. Any ideas will be very helpful. Thanks Bill |
#2
|
|||
|
|||
"=?Utf-8?B?d3N0b2thcno=?=" wrote in
: I'm trying to get a one to many relationship between four tables. No: you might want a one-to-many-to-many-to-many model though. Each table is embedded within the other. No again: every table is pure independent and self contained on its own. Just becuase MS's horrid "subdatasheets" make it look like something else, don't let them fool you. The process I wish to accomplish is to have my primary table with the primary key pull data from another table, the linked table will then pull data from another table linked to it. No yet again: Tables don't "pull" anything, ever. They only hold data, in nice neat rows called records. Basically The first table can have only one work order, the next table can have many item numbers based on the work order, the last table wil have many layer numbers based on both one work order and the many item numbers. I seem to have read something about this further up, but what you have described _here_ is really simple: WorkOrders(*WONumber, SizeOfJob, DateOrdered, etc) Items(*WONumber+, *LineNumber, DesciptionOfItem, CostOfItem, Width) Layers(*WONumber+, *LineNumber+, *LayerCode, Transparency, Thickness) The (*) asterisks refer to primary keys: note that Items and Layers have compound PKs made up of two or three fields. The (+) plusses refer to foreigh keys: in other words, you can't have an Item that does not have a valid WONumber; and you can't have a Layer that isn't attached to an Item wit a real (WONumber, LineNumber) combination. Important note: there is _no_ relationship directly enforced between Layers(WONumber) and WorkOrders(WONumber). Now, to see all the layers attached to each Item attached to each WO, then it's easy to put them together in a query. In a form, you could use a form-subform-subform arrangement, although that gets messy for my taste. If you are finding this hard, you might like to read some basic grounding in R theory and database design. You will really need to understand this stuff in order to get anything useful out of Access. What happened to the fourth table? Hope that helps Tim F I have tried junction tables and using primary keys. Neither have worked for me. Any ideas will be very helpful. Thanks Bill |
#3
|
|||
|
|||
Thank you for the replay.
Sorry for using generic terms like "pull data". I have taken 2 courses in MS office when I was in college. Finding out that really did not help in my situation. I have treid the Junction Table path. I cannot use a primary key in the Item table and the Layer table. I must be able to have many entries of the same Work ID In both the Item Table and Layer Table as well as many items and layers. I am using Subforms within my parent form. Which work fine if I use primary keys but limit me to only one Item, layer per Work ID. The fourth table I ommited due to the fact i need to get this operational first. The fourt table is a volume calculater based on the layer table. Currently in my junction table I have Work ID, Item Num, LayerNum. Thanks Bill "Tim Ferguson" wrote: "=?Utf-8?B?d3N0b2thcno=?=" wrote in : I'm trying to get a one to many relationship between four tables. No: you might want a one-to-many-to-many-to-many model though. Each table is embedded within the other. No again: every table is pure independent and self contained on its own. Just becuase MS's horrid "subdatasheets" make it look like something else, don't let them fool you. The process I wish to accomplish is to have my primary table with the primary key pull data from another table, the linked table will then pull data from another table linked to it. No yet again: Tables don't "pull" anything, ever. They only hold data, in nice neat rows called records. Basically The first table can have only one work order, the next table can have many item numbers based on the work order, the last table wil have many layer numbers based on both one work order and the many item numbers. I seem to have read something about this further up, but what you have described _here_ is really simple: WorkOrders(*WONumber, SizeOfJob, DateOrdered, etc) Items(*WONumber+, *LineNumber, DesciptionOfItem, CostOfItem, Width) Layers(*WONumber+, *LineNumber+, *LayerCode, Transparency, Thickness) The (*) asterisks refer to primary keys: note that Items and Layers have compound PKs made up of two or three fields. The (+) plusses refer to foreigh keys: in other words, you can't have an Item that does not have a valid WONumber; and you can't have a Layer that isn't attached to an Item wit a real (WONumber, LineNumber) combination. Important note: there is _no_ relationship directly enforced between Layers(WONumber) and WorkOrders(WONumber). Now, to see all the layers attached to each Item attached to each WO, then it's easy to put them together in a query. In a form, you could use a form-subform-subform arrangement, although that gets messy for my taste. If you are finding this hard, you might like to read some basic grounding in R theory and database design. You will really need to understand this stuff in order to get anything useful out of Access. What happened to the fourth table? Hope that helps Tim F I have tried junction tables and using primary keys. Neither have worked for me. Any ideas will be very helpful. Thanks Bill |
#4
|
|||
|
|||
On Mon, 24 Jan 2005 10:01:03 -0800, "wstokarz"
wrote: I have treid the Junction Table path. I cannot use a primary key in the Item table and the Layer table. I must be able to have many entries of the same Work ID In both the Item Table and Layer Table as well as many items and layers. On the contrary: YOU MUST have a primary key in EVERY table. You may be making the incorrect assumption that a primary key must consist of one field. In Access, a Primary Key can consist of up to *ten* fields. If, as suggested, you make the Primary Key of the Layer table consist of *THREE* fields - the WorkID, the ItemNo, and the LayerNo - then any one of these can have multiple records with the same value; that is, you can have many layers for a given WorkID. However, you will not be able to enter two records with identical values of WorkID, ItemNo, and LayerNo, which is exactly the effect you want. Multiple items per WorkID, multiple layers per Item, but only one unique combination of the three fields. John W. Vinson[MVP] |
#5
|
|||
|
|||
"=?Utf-8?B?d3N0b2thcno=?=" wrote in
: WorkOrders(*WONumber, SizeOfJob, DateOrdered, etc) Items(*WONumber+, *LineNumber, DesciptionOfItem, CostOfItem, Width) Layers(*WONumber+, *LineNumber+, *LayerCode, Transparency, Thickness) I cannot use a primary key in the Item table and the Layer table. I must be able to have many entries of the same Work ID In both the Item Table and Layer Table as well as many items and layers. It is frustrating when people give no evidence of having read the replies to their questions. If you actually do look at the suggestion above, you'll see that Items has a PK made up of (WONumbner, LineNumber) which means that you can have as many Items as you like with the same WONumber as long as they have different LineNumbers. Similar with Layers. I'd like to take you back over something else in my reply: If you are finding this hard, you might like to read some basic grounding in R theory and database design. You will really need to understand this stuff in order to get anything useful out of Access. You really have to understand what a table is, what a PK does, how to make relationships etc to do anything useful with Access. It's a very different beast from the rest of the Office package, which is why it's not included in Office Standard -- at last MS got the message that it's unsuitable for naive end-users, unlike Word and Excel. IMO, I have sat through so many teeth-grindingly awful Powerpoint presentations, I am starting to think that it should be withdrawn to Office Professional too... Currently in my junction table I have Work ID, Item Num, LayerNum. This appropriate only if you have a situation where a Layer can be applied to a WorkOrder any number of times, but only once for each Item -- although I don't know much about what you are trying to model, I would find it hard to visualise how that would be a good scheme. All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Help with table design and relationships | Richard Wright | Database Design | 3 | June 20th, 2004 03:49 PM |