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
|
|||
|
|||
Inventory Control Template Problem
I created an Inventory Control Database using the template in Access 2000. My
only problem is that the products table does not meet my needs. I need to replace the Products table with about five new tables. I want to start small so for now I have replaced The Products table with two tables, one called Drills, and one called Inserts. First, how do I properly relate the two new tables to the Inventory Transactions table? If I can get that far I should be able to handle the rest. Thanks |
#2
|
|||
|
|||
Inventory Control Template Problem
On Sun, 2 Dec 2007 14:14:01 -0800, rsm169
wrote: I created an Inventory Control Database using the template in Access 2000. My only problem is that the products table does not meet my needs. I need to replace the Products table with about five new tables. I want to start small so for now I have replaced The Products table with two tables, one called Drills, and one called Inserts. First, how do I properly relate the two new tables to the Inventory Transactions table? If I can get that far I should be able to handle the rest. Thanks Are the *structures* - fieldnames and field definitions - of these five tables identical? If so, you're on the wrong track: you should indeed have only one parts table, with perhaps another field to indicate PartType (drills, end mills, inserts, ...) If the five tables are in fact of different structure, you may have some additional complexity. You cannot enforce relational integrity from one Inventory Transactions table to (one of these five tables). What you may need to do is have a single Parts table related one to many to Inventory Transactions, and containing those fields that are in common among the five types of parts; this table would be related one-to-one to each of the five specific tables, each of which would contain fields particular to drills, or inserts, or whatever. This technique is called "Subclassing" and is one of the few cases where one to one relationships are appropriate. John W. Vinson [MVP] |
#3
|
|||
|
|||
Inventory Control Template Problem
John,
Thank you for your help. My file structures are very much different. I have never heard of subclassing; it is now my new buzzword! I am going to research this topic and see if I can make any head way. Regards "John W. Vinson" wrote: On Sun, 2 Dec 2007 14:14:01 -0800, rsm169 wrote: I created an Inventory Control Database using the template in Access 2000. My only problem is that the products table does not meet my needs. I need to replace the Products table with about five new tables. I want to start small so for now I have replaced The Products table with two tables, one called Drills, and one called Inserts. First, how do I properly relate the two new tables to the Inventory Transactions table? If I can get that far I should be able to handle the rest. Thanks Are the *structures* - fieldnames and field definitions - of these five tables identical? If so, you're on the wrong track: you should indeed have only one parts table, with perhaps another field to indicate PartType (drills, end mills, inserts, ...) If the five tables are in fact of different structure, you may have some additional complexity. You cannot enforce relational integrity from one Inventory Transactions table to (one of these five tables). What you may need to do is have a single Parts table related one to many to Inventory Transactions, and containing those fields that are in common among the five types of parts; this table would be related one-to-one to each of the five specific tables, each of which would contain fields particular to drills, or inserts, or whatever. This technique is called "Subclassing" and is one of the few cases where one to one relationships are appropriate. John W. Vinson [MVP] |
#4
|
|||
|
|||
Inventory Control Template Problem
John,
I hope you are still with me here. I tried to do some research on subclassing and came up pretty dry. I typed it into Access's help and came up with nothing. I got some info from the net but it was not very helpful to me. From reading your post I created an Intermediate table called "Parts", it will have a one to many relationship to the table Inventory Transactions and a one to one relationship with the two subclass tables. Have I understood this correctly? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsID InsertsID DrillsTBL DrillsID Ect. InsertsTBL InsertsID Ect. "John W. Vinson" wrote: On Sun, 2 Dec 2007 14:14:01 -0800, rsm169 wrote: I created an Inventory Control Database using the template in Access 2000. My only problem is that the products table does not meet my needs. I need to replace the Products table with about five new tables. I want to start small so for now I have replaced The Products table with two tables, one called Drills, and one called Inserts. First, how do I properly relate the two new tables to the Inventory Transactions table? If I can get that far I should be able to handle the rest. Thanks Are the *structures* - fieldnames and field definitions - of these five tables identical? If so, you're on the wrong track: you should indeed have only one parts table, with perhaps another field to indicate PartType (drills, end mills, inserts, ...) If the five tables are in fact of different structure, you may have some additional complexity. You cannot enforce relational integrity from one Inventory Transactions table to (one of these five tables). What you may need to do is have a single Parts table related one to many to Inventory Transactions, and containing those fields that are in common among the five types of parts; this table would be related one-to-one to each of the five specific tables, each of which would contain fields particular to drills, or inserts, or whatever. This technique is called "Subclassing" and is one of the few cases where one to one relationships are appropriate. John W. Vinson [MVP] |
#5
|
|||
|
|||
Inventory Control Template Problem
On Tue, 4 Dec 2007 17:06:00 -0800, rsm169
wrote: From reading your post I created an Intermediate table called "Parts", it will have a one to many relationship to the table Inventory Transactions and a one to one relationship with the two subclass tables. Have I understood this correctly? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsID InsertsID DrillsTBL DrillsID Ect. InsertsTBL InsertsID Ect. Not quite. A one to one relationship still has a parent and child. The parent in this case is PartsTBL - its primary key should be PartsID, and it should NOT contain a DrillsID or an InsertsID. Instead, the "child" tables, DrillsTBL and InsertsTBL, should have the PartsID as their primary key (and also as a foreign key to PartsTBL. After all, every Drill is a Part; but not every Part is a Drill. So the Parts table shouldn't contain any information specific to drills - only that information which is pertinant to all kinds of parts. The one exception might be to consider having a (formally redundant) PartType field in the Parts table, with values "Drill", "Insert", etc. This would let you add VBA code to your form to select the proper subform based on the PartType and validate that you're not (say) assigning Insert information to a part which is actually a Drill. This has been a drill. If this had been a real emergency, ... oops. sorry. John W. Vinson [MVP] |
#6
|
|||
|
|||
Inventory Control Template Problem
LOL, pun appreciated.
So the tables should be more like this? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsTBL DrillsID PartsID Ect. InsertsTBL InsertsID PartsID Ect. When I create the one to many relationship for the parts and inventory transaction tables I think should I check all three referental integrity boxes and select join type #1 in join properties. Is this correct? How about the one to one relationships, I have no idea what to do there. Thanks, Mike "John W. Vinson" wrote: On Tue, 4 Dec 2007 17:06:00 -0800, rsm169 wrote: From reading your post I created an Intermediate table called "Parts", it will have a one to many relationship to the table Inventory Transactions and a one to one relationship with the two subclass tables. Have I understood this correctly? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsID InsertsID DrillsTBL DrillsID Ect. InsertsTBL InsertsID Ect. Not quite. A one to one relationship still has a parent and child. The parent in this case is PartsTBL - its primary key should be PartsID, and it should NOT contain a DrillsID or an InsertsID. Instead, the "child" tables, DrillsTBL and InsertsTBL, should have the PartsID as their primary key (and also as a foreign key to PartsTBL. After all, every Drill is a Part; but not every Part is a Drill. So the Parts table shouldn't contain any information specific to drills - only that information which is pertinant to all kinds of parts. The one exception might be to consider having a (formally redundant) PartType field in the Parts table, with values "Drill", "Insert", etc. This would let you add VBA code to your form to select the proper subform based on the PartType and validate that you're not (say) assigning Insert information to a part which is actually a Drill. This has been a drill. If this had been a real emergency, ... oops. sorry. John W. Vinson [MVP] |
#7
|
|||
|
|||
Inventory Control Template Problem
On Thu, 6 Dec 2007 18:21:00 -0800, rsm169
wrote: LOL, pun appreciated. So the tables should be more like this? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsTBL DrillsID PartsID Ect. InsertsTBL InsertsID PartsID Ect. It's probably not necessary to have separate DrillsID and InsertsID fields. Instead, make PartsID the Primary Key of PartsTBL, DrillsTBL and InsertsTBL. You already HAVE a unique ID - a given PartsID can apply to only one part, so you don't need another field. When I create the one to many relationship for the parts and inventory transaction tables I think should I check all three referental integrity boxes and select join type #1 in join properties. Is this correct? If PartsID is an Autonumber, then check only the Enforce Referential Integrity field. The Cascade Updates feature will never be needed with an autonumber; you can't edit an autonumber value so there's nothing to cascade. You can check Cascade Deletes if you wish... it's a bit dangerous because it will delete records from two (or more) tables when you delete from the Parts table; it might be safer to have the user warned by saying "you can't delete this record from PartsTBL because there's a matching record in DrillsTBL", and then have them explicitly choose to delete the Drills record before deleting the Parts. That's really up to you. How about the one to one relationships, I have no idea what to do there. Drag the PartsID from PartsTBL to the PartsID (primary key!!) in DrillsTBL; and from PartsTBL to the PartsID in InsertsTBL; and so on through all the child tables. This will ensure that you must first create a Parts record, and then specify what type of part it is by creating a record in one of the child tables (using a Subform, or by basing your parts entry forms on queries joining PartsTBL to the specific table). John W. Vinson [MVP] |
#8
|
|||
|
|||
Inventory Control Template Problem
John,
Things have been working well for me after following your great advice. I am able to enter and delete data and I believe everything is working properly. I have an additional problem with my database and I am unsure if it is a design problem or a form problem. I created a form that shows parent table records in the header using text boxes and child table records in the footer by using tabs. When I enter data in the parent table, it is possible to enter data into all of the child tables creating one large, wrong, record. How can I restrict the user so they can enter data in the parent table and only one child table per record? Thanks, Mike "John W. Vinson" wrote: On Thu, 6 Dec 2007 18:21:00 -0800, rsm169 wrote: LOL, pun appreciated. So the tables should be more like this? InventoryTransActionsTBL InventoryTransActionsID PartsID PartsTBL PartsID PartsName DrillsTBL DrillsID PartsID Ect. InsertsTBL InsertsID PartsID Ect. It's probably not necessary to have separate DrillsID and InsertsID fields. Instead, make PartsID the Primary Key of PartsTBL, DrillsTBL and InsertsTBL. You already HAVE a unique ID - a given PartsID can apply to only one part, so you don't need another field. When I create the one to many relationship for the parts and inventory transaction tables I think should I check all three referental integrity boxes and select join type #1 in join properties. Is this correct? If PartsID is an Autonumber, then check only the Enforce Referential Integrity field. The Cascade Updates feature will never be needed with an autonumber; you can't edit an autonumber value so there's nothing to cascade. You can check Cascade Deletes if you wish... it's a bit dangerous because it will delete records from two (or more) tables when you delete from the Parts table; it might be safer to have the user warned by saying "you can't delete this record from PartsTBL because there's a matching record in DrillsTBL", and then have them explicitly choose to delete the Drills record before deleting the Parts. That's really up to you. How about the one to one relationships, I have no idea what to do there. Drag the PartsID from PartsTBL to the PartsID (primary key!!) in DrillsTBL; and from PartsTBL to the PartsID in InsertsTBL; and so on through all the child tables. This will ensure that you must first create a Parts record, and then specify what type of part it is by creating a record in one of the child tables (using a Subform, or by basing your parts entry forms on queries joining PartsTBL to the specific table). John W. Vinson [MVP] |
#9
|
|||
|
|||
Inventory Control Template Problem
Ok here is what I have been able to do..
I have Staff 1:m Staff/Radio m:1 Radio. Staff/radio has Date/Time Out: and Date/Time In.. I still haven't gotten it working past that. And unfortunately my instructor is not very helpful in this regard. Any more help you would be willing to give would be appricated. Jason "rsm169" wrote: I created an Inventory Control Database using the template in Access 2000. My only problem is that the products table does not meet my needs. I need to replace the Products table with about five new tables. I want to start small so for now I have replaced The Products table with two tables, one called Drills, and one called Inserts. First, how do I properly relate the two new tables to the Inventory Transactions table? If I can get that far I should be able to handle the rest. Thanks |
Thread Tools | |
Display Modes | |
|
|