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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Joins
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 |
#3
|
|||
|
|||
Joins
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 |
#4
|
|||
|
|||
Joins
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 |
#5
|
|||
|
|||
Joins
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. |
#6
|
|||
|
|||
Joins
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 |
#7
|
|||
|
|||
Joins
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 |
#8
|
|||
|
|||
Joins
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 |
#9
|
|||
|
|||
Joins
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 |
#10
|
|||
|
|||
Joins
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 |
|
Thread Tools | |
Display Modes | |
|
|