Joins

I have 3 production tables, A, B, C. Each has a PK, to act as parent fields.
( I have 3 production tables b/c each table stores diff types of info for the
diff items.) I have another table where the quantity of each item made is
stored (this is done so I can do a calculated query), it's PK is the
collection of PKs from A, B and C tables and acts as the child field.

Collectively, bxt tables A,B and C, and the inventory table their will be no
childless parents or orphans. But the individuals joins, from each
production table to the inventory table, will have orphans.

How do I relate the tables so I can reinforce referential intergrity and
cascade update related fields? Or can I?

Thanks

I'm not sure your "child" table really is...

Are you describing a situation in which you have "Things Produced", and
"Things are of type A, B, or C" (each with differing characteristics)? If
so, this sounds like a sub-type situation, and the table you called a
"child" is actually the only place were all "Things Produced" are listed.
The other three tables only hold "Things of Type A information" (or B or C).

I'm not clear on your "inventory" table. Is this a table that has a row for
each production run (i.e., "on this date, this many of TypeA were made")?

My definition of inventory is how many made minus how many sent out/used, so
this "calculated" value doesn't need storing. Where are you keeping the
"how many of TypeA we shipped and when" information?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message ...
I have 3 production tables, A, B, C. Each has a PK, to act as parent
fields.
( I have 3 production tables b/c each table stores diff types of info for
the
diff items.) I have another table where the quantity of each item made is
stored (this is done so I can do a calculated query), it's PK is the
collection of PKs from A, B and C tables and acts as the child field.

Collectively, bxt tables A,B and C, and the inventory table their will be
no
childless parents or orphans. But the individuals joins, from each
production table to the inventory table, will have orphans.

How do I relate the tables so I can reinforce referential intergrity and
cascade update related fields? Or can I?

Thanks

What do you mean when you say the PK of the "child" table is the
collection of the PK's from tables A, B and C? The inventory table would
have to have another field in its PK to make it unique. Does each
inventory record relate to only one production table? If so, how does
the inventory table know which of tables A, B or C each record relates
back to?

Can you provide the structure of the inventory table, to give us a
better idea of what you are trying to accomplish?

John


Access Greenhorn wrote:

I have 3 production tables, A, B, C. Each has a PK, to act as parent fields.
( I have 3 production tables b/c each table stores diff types of info for the
diff items.) I have another table where the quantity of each item made is
stored (this is done so I can do a calculated query), it's PK is the
collection of PKs from A, B and C tables and acts as the child field.

Collectively, bxt tables A,B and C, and the inventory table their will be no
childless parents or orphans. But the individuals joins, from each
production table to the inventory table, will have orphans.

How do I relate the tables so I can reinforce referential intergrity and
cascade update related fields? Or can I?

Thanks

The set up is such. Each "production" table (A,B,C) holds information unique
to item A, B or C. So it is undesirable to have only one "production" table
since that would result in a lot of N/A in the rows.
The rows describe the production of one batch of item A (B or C) and is
assigned a unique batch number or lot number (PK). When the stock of that
batch is depleted, the information is kept for archival purposes. So item A
is replenished but not the batch, batches are simply made and depleted.

Another table keeps track of to whom and what was sent out, again items
being tracked by their batch/lot numbers (production PKs). This information
is also kept for archival purposes.

And so I can calculate how much stock is on hand (stock of each batch, not
item) the quantity made (of a batch) is stored in the "Inventory" table, as
is a Lossed/Tossed field. The PK in this table is therefore made up of the
PKs of the production tables.
So I never store Quantity on Hand, I simply calculate it as needed.

If you put into the search of this discussion group "Summation fails if Nz
used" you will see another one of my postings regarding the code behind my
Quantity on Hand Query. (To give you a better idea if needed.)

I hope this clarifies everything.

Thanks for looking at this,
Access Greenhorn

Keep in mind that the "Summation Fails if Nz used" uses tables made up on the
fly in a junk database to test out the query; so it's tables are not accurate
to the actual database being built.

AG,

Is it too late to consider one and only one Inventory Item table? Don't
worry too much if the unused or inapprpriate columns are text as Access will
compress these. The usual approach would be to have one table and a number
of queries based on that table to give you the different views you need for
the different inventory types.

However if you convinced you need to split the data into multiple tables
then each table should have the same primary key. There is no question here
of parents and children; everything has a onene relationship.

Regards,

Rod

"Access Greenhorn" wrote:

The set up is such. Each "production" table (A,B,C) holds information unique
to item A, B or C. So it is undesirable to have only one "production" table
since that would result in a lot of N/A in the rows.
The rows describe the production of one batch of item A (B or C) and is
assigned a unique batch number or lot number (PK). When the stock of that
batch is depleted, the information is kept for archival purposes. So item A
is replenished but not the batch, batches are simply made and depleted.

Another table keeps track of to whom and what was sent out, again items
being tracked by their batch/lot numbers (production PKs). This information
is also kept for archival purposes.

And so I can calculate how much stock is on hand (stock of each batch, not
item) the quantity made (of a batch) is stored in the "Inventory" table, as
is a Lossed/Tossed field. The PK in this table is therefore made up of the
PKs of the production tables.
So I never store Quantity on Hand, I simply calculate it as needed.

If you put into the search of this discussion group "Summation fails if Nz
used" you will see another one of my postings regarding the code behind my
Quantity on Hand Query. (To give you a better idea if needed.)

I hope this clarifies everything.

Thanks for looking at this,
Access Greenhorn

Hey Rod,

Unless someone cleverer than I can come up with a work around it looks like
my only option, if I want to be able to reinforce referential intergrity, is
to create one production table. But this is highly undesirable. This
results in only 6 common fields and 20 fields compiled from the 5 separate
production tables, which in turn will result in some entries literally only
having as little as 2 of those 20 fields filled with data.

This single table would then create no parent-child issues. Separate
production tables would because when you look at the relationship of EACH
individual production table to the Inventory/QuantityMade table you are
dealing with orphans.

Unless someone knows of a way to create a relationship with all 5 production
tables on the one side and the Inventory/QuantityMade table on the other
side. Anyone?

AG

Have you revisited the lead on sub-typing? If you were working only with a
spreadsheet, you'd have to have multiple empty columns to cover all
possibilities.

If you are using a relational database (Access), you can create a "main"
table with only a couple fields, and sub-type tables (one for each sub-type)
that hold the attributes unique to each sub-type.

Is there a reason this approach won't work for you?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message ...
Hey Rod,

Unless someone cleverer than I can come up with a work around it looks
like
my only option, if I want to be able to reinforce referential intergrity,
is
to create one production table. But this is highly undesirable. This
results in only 6 common fields and 20 fields compiled from the 5 separate
production tables, which in turn will result in some entries literally
only
having as little as 2 of those 20 fields filled with data.

This single table would then create no parent-child issues. Separate
production tables would because when you look at the relationship of EACH
individual production table to the Inventory/QuantityMade table you are
dealing with orphans.

Unless someone knows of a way to create a relationship with all 5
production
tables on the one side and the Inventory/QuantityMade table on the other
side. Anyone?

AG

Jeff, you possibly give me hope.

I am not familiar with sub-typing in an Access context. I'm assuming that I
would put the fields common to all the production tables into one table and
then class each type of item as a subtype and hold the details of the batches
in the subtype tables.

But how is this all structured such that I would avoid my current problem of
not being able to reinforce referential integrity due to orphans?

Thanks,
AG

If your main table holds the MainID and ThingType, and that's it, one of
your sub-tables can hold a SubTableID, MainID, and all the attributes
specific to that table's sub-type. Each of the others hold the same.

I don't see how you'd end up with orphans if you first create a MainID for
the Thing, then add a sub-table record with the MainID (as a foreign key),
it's own SubTableID (the primary key), and whatever attributes apply.

The next Thing you add to the Main table might be a different type, so after
adding a MainID in the Main table, you'd have to add the new sub-table
record in the appropriate (different) subtable.

Where are the orphans coming in?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message news
Jeff, you possibly give me hope.

I am not familiar with sub-typing in an Access context. I'm assuming that
I
would put the fields common to all the production tables into one table
and
then class each type of item as a subtype and hold the details of the
batches
in the subtype tables.

But how is this all structured such that I would avoid my current problem
of
not being able to reinforce referential integrity due to orphans?

Thanks,
AG