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 |
#11
|
|||
|
|||
Joins
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. "Jeff Boyce" wrote: 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 |
#12
|
|||
|
|||
Joins
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 "Rod Plastow" wrote: 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. "Jeff Boyce" wrote: 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 |
#13
|
|||
|
|||
Joins
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 |
#14
|
|||
|
|||
Joins
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 |
#15
|
|||
|
|||
Joins
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 onene 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 "Access Greenhorn" wrote: 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 |
#16
|
|||
|
|||
Joins
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. |
|
Thread Tools | |
Display Modes | |
|
|