 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2006Joins
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 >I have 3 production tables, A, B, C. Each has a PK, to act as parent >fields. [quoted text clipped - 13 lines] > > 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 > 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 [quoted text clipped - 10 lines] > > 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 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 one:one relationship. Regards, Rod > 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 [quoted text clipped - 22 lines] > 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 > Hey Rod, > [quoted text clipped - 19 lines] > > 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 > Jeff, you possibly give me hope. > [quoted text clipped - 12 lines] > Thanks, > AG AG / Jeff, As I understand it the requirement is not uncommon. Many industries - aircraft spares, pharmaceuticals , foodstuffs - need to track their production and distribution by batch. Where this requirement seems a little different is that there are three production processes that need (?) to be kept separate. I went back to the drawing board and came up with four (perhaps five) tables as follows. Inventory Item. This table has a PK that is the item id and contains all common attributes such as description. It is I think akin to Jeff's 'Main' table. Production Process (or Type). AG, this is akin to your A, B & C. I would expect three rows on this table. The Inventory Item table contains a foreign key to the Production Process if each item is manufactured by one and only one process. Otherwise you need to resolve the many:many relationship with a cross-reference table. (Hence the possible fifth table mentioned above.) Batch. This is a child table of Production Process. If your batch numbering is unique across all processes then the batch number is sufficient as a PK, otherwise you need to append the PK of Production Process. The table attributes include a foreign key for Inventory Item. The table contains all the production related data. Inventory. This has the same key(s) as Batch and strictly 3rd Normal Form suggests both tables should become one. However there is a case for separating the data as the purpose of the two tables and their life cycles are completely different. This table also has Inventory Item as a foreign key. The table contains all the storage/stocking related data, shelf lives, quantities on hand, etc. If you follow all the relationships I think there will be no problem in enforcing relational integrity and specifying cascade update and delete as appropriate. This proposal does not solve the redundancy problem that will occur on the Batch table, but I believe the extent of that problem is now much reduced. If its still bad then sub typing is the way to go. Hope this helps, Rod PS Sorry AG, I cannot follow up you Summation post as the search engine keeps hanging. > 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 [quoted text clipped - 31 lines] > > Thanks, > > AG Hi again, The search engine has at last located your 'Summation' post; I copy and paste the SQL here. SELECT tblInventoryA.Batchcode, tblProd1A.QtyMade, Sum(tblKitsSent.QtySent) AS SumOfQtySent, tblInventoryA.LossedorTossed, [QtyMade]-nz([QtySent])-nz([LossedorTossed]) AS OnHand FROM tblProd1A LEFT JOIN (tblInventoryA LEFT JOIN tblKitsSent ON tblInventoryA.Batchcode=tblKitsSent.Batchcode) ON tblProd1A.Batchcode=tblInventoryA.Batchcode GROUP BY tblInventoryA.Batchcode, tblProd1A.QtyMade, tblInventoryA.LossedorTossed, [QtyMade]-nz([QtySent])-nz([LossedorTossed]); What you are trying to do is obvious. Why you need the NZ function is also obvious. However I stared long and hard at the code wondering why it worked at all and why you did not keep getting the aggregate function message. OK, let me suggest some SQL code that will do what you want provided that tblInventoryA has zero or one rows for the BatchCode, that is there are not multiple fields for LossedorTossed for each BatchCode. Oh, by the way, if you omit the second argument of NZ the function returns a zero-length string which is not what you want when summing numbers SELECT tblProd1A.Batchcode, Sum(NZ(tblKitsSent.QtySent,0)) AS TotalSent, First(NZ(tblInventoryA.LossedorTossed,0)) AS QtyScrapped, First(NZ(tblProd1A.QtyMade,0)) AS TotalMade, [TotalMade]-[TotalSent]-[QtyScrapped] AS QtyOnHand FROM (tblProd1A LEFT JOIN tblInventoryA ON tblProd1A.BatchCode = tblInventoryA.BatchCode) LEFT JOIN tblKitsSent ON tblInventoryA.BatchCode = tblKitsSent.Batchcode GROUP BY tblProd1A.BatchCode; The use of NZ for the QtyMade aggregate is probably redundant; there will always be a non null value. You could consider changing the join between tblProd1A and tblInventoryA to be INNER JOIN if you only want rows in your result set where an inventory record exists as well as a production record. Have fun. Rod > AG / Jeff, > [quoted text clipped - 78 lines] > > > Thanks, > > > AG Mucho gracias, merci beaucoup and dziękuję bardzo Rod. This query was driving me nutty; not knowing why it wasn't working. I will definitely implement your improved code in the database when the time comes. And you are right on your assumptions, tblInventoryA has one row per batchcode and there are not multiple fields/(rows) for LossedorTossed for each batchcode. AG Rod, You called it pretty close. I work in Virology and am designing the database to keep track of reagents to be used in diagnostic tests. What I ended up doing and if you can check it over for any problems I may be failing to foresee, I have 6 production tables that hold the details of each batch of 6 different classes of reagents. They are all joined to a table called QtyMade that holds all of the common information and, of course, how much was made. The PK of the QtyMade table is comprised of the PKs of the production tables and acts as the parent in a left join to the child production tables. I then have an Inventory table that holds temporary information like where the reagents are stored. It also is the child in a left join to the QtyMade table. The QtyMade table is also a parent in a left join to the Package Details table that keeps track of what and how may reagents were sent in a package. Any problems with this setup? Thanks all, AG Hi AG, I've been thinking about this one all morning, trying to visualise your design. I think I now have it. The unusual feature is these 6 production tables but I can understand how you arrived at this if you are worried about redundancy - and this debate about redundancy is the crux of the whole matter, but let's leave the debate to one side for the moment. What are the adverse consequences of having 6 tables rather than one? I suggest that if in future you need to add a seventh reagent class/table then this will be more difficult than adding additional columns to an existing table. Then there is the difficulty and additional complexity you have already experienced of defining relationships and queries. Apart from these two points I can see no obvious 'minefields' ahead. I believe the relationships you describe to be incorrect but before explaining what I think they ought to be we have to be clear whether we are talking about database design or query design. In database design I prefer to use the term relationship. A relationship is a permanent association between two tables based on key values and usually describes the real-life nature of of the data interrelationships. You can define one and only one relationship between two tables. If you wish - and I can see no point in defining relationships otherwise - the database manager will ensure referential integrity, et., etc. Database designers will refer to relationships in terms of one-to-many, one-to-one, etc. In query design I prefer the term join. Joins are temporary, transient associations for the purpose of that query and only that query. You may define as many joins between two tables as you wish. The joins can be contrary to the database relationships! Access uses the terminology of LEFT/RIGHT JOIN and INNER JOIN. I want to talk about your database relationships. Each row on each of your 6 production tables describes a production batch. Each row on your QtyMade table describes additional common data for each production batch. The inference is that the total count of rows across all 6 production tables should equal the count of rows on the QtyMade table; a pair of rows is needed to completely describe the batch. Each pair of rows should have the same primary key, probably BatchCode. (I think this is what you are saying but I'm not too clear about it.) Now, because QtyMade contains a row for every batch irrespective of its reagent class, the primary key, BatchCode has to be unique across your application. When you come to defining your database relationships it makes no sense to nominate one side as 'parent' and the other as 'child.' What you need is a one-to-one relationship between the QtyMade table and each of your production tables. If you get it right you will see a number 1 at each end of the line joining the tables in the Access Relationships diagram. If you store a batch of reagent in more than one location then you need a one:many relationship between your batch and the inventory location. However if you always store a batch together in one and only one location then why not simply add the location attributes to the QtyMade table? But if you insist on a separate table then again it is a one:one relationship and I suggest the primary key of the Inventory table is also BatchCode. Finally there is the Package that I like to think of as a shipment. The Package has a many:many relationship with batches so you need to resolve this with a cross reference/linking table: Package has a one:many relationship with your linking table and similarly QtyMade has a one:many relationship with your linking table. You can construct your linking table with a compound key of BatchCode and PackageId or give it a meaningless primary key (autonumber) and include both BatchCode and PackageId as foreign keys. That's enough for now. Regards, Rod > Rod, > [quoted text clipped - 20 lines] > Thanks all, > AG You assumptions are correct up until the last paragraph. I am talking about relationships and across all 6 tables and the QtyMade table the rows will be equal. I have set them in a one to one relationships with a left join type (sorry I know you prefer to reserve the word join to queries but that is how access describes it). And the PK across the 6 tables are unique from eachother. The batches/items are not stored in more than one location but I also don't need to hand onto that information so it is in a separate table so it can be purged when we no longer have those items on the shelves. That relationship is also a left one-to-one. And I have a one-to-many relationship bxt the QtyMade table and the PackageDetails table, since the batches will be sent out in increments, so to speak. So...I think I'm safe. From what I have gathered from your response. Thanks, AG P.S. Could you look at another posting of mine, I need some assistance on code. Search "Cascading Combo box and additional parameter". Thanks again. 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.