4 table relationships

[wstokarz]

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

 

[Tim Ferguson]

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

 

[wstokarz]

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

 

[John Vinson]

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]

 

[Tim Ferguson]

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:

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