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
|
|||
|
|||
Design questions
I have successfully completed several smaller databases
that work quite well. Now I am undertaking the largest project so far, and I have some design questions before I have completed the first table. My company performs a variety of manufacturing processes on a variety of Components. The process is detailed in a Process Plan, each of which is numbered in the format 04- 20 (for the twentieth plan developed in 2004). Each Process Plan can be for one or more Components. The same process on two different Components could be two separate Process Plans, or it could be the same Process Plan. Also, one component could be processed according to several different Process Plans. Also, each Process Plan references one or more Specifications, but probably no more than 5. Here is what I have so far, ignoring for a moment the Specifications: tblProcessPlan PlanID (PK) Component ID (FK) PlanNumber (could probably be the PK, but I'm not sure) Revision Date Process Archive (Y/N, for queries) tblComponent ComponentID (PK) PlanID (FK) ComponentNumber (it is possible that 2 manufacturers will use the same number, so no PK here) ComponentName There are other fields, but this is enough for the question. When somebody looks up a Component (by selecting from a list), they should be able to see what Process Plans are associated with it. From there they will need to see the details of the selected Process Plan, including a listing of all Components that may be processed according to that plan. Each Process Plan can be for many Components, and each Component can be associated with many Process Plans, but I don't think this makes it a many-to-many relationship. I can't imagine a field in a junction table that would not be in one of the other two tables. Instead, I see tblProcessPlan as the record source for frmProcessPlan, and tblComponents as the record source for a subform fsubComponents. If I am still on track here, the Specifications are the next step. I think would have a Specifications table, with its PK a FK in tblProcessPlan. Each Process Plan can reference many Specifications, and each Specification can be associated with many Process Plans. As with Components, looking up a Specification would produce a listing of associated Process Plans. This is necessary because a revised Specification could mean revising the associated Process Plans. Am I heading in the right general direction with this? Any comments or suggestions? |
#2
|
|||
|
|||
Design questions
comments inline:
"Bruce" wrote in message ... I have successfully completed several smaller databases that work quite well. Now I am undertaking the largest project so far, and I have some design questions before I have completed the first table. My company performs a variety of manufacturing processes on a variety of Components. The process is detailed in a Process Plan, each of which is numbered in the format 04- 20 (for the twentieth plan developed in 2004). Each Process Plan can be for one or more Components. The same process on two different Components could be two separate Process Plans, or it could be the same Process Plan. Also, one component could be processed according to several different Process Plans. Also, each Process Plan references one or more Specifications, but probably no more than 5. Here is what I have so far, ignoring for a moment the Specifications: tblProcessPlan PlanID (PK) Component ID (FK) PlanNumber (could probably be the PK, but I'm not sure) Revision Date Process Archive (Y/N, for queries) so this allows you to link each specific process plan to a single specifc component. but how do you plan to link the same process plan to another specific component? in this two-table setup, you'd have to enter a duplicate process plan record for each component that plan was associated with. that solution violates table normalization rules. tblComponent ComponentID (PK) PlanID (FK) ComponentNumber (it is possible that 2 manufacturers will use the same number, so no PK here) ComponentName again this allows you to link each specific component to a single specifc process plan. and again, you'd have to enter a duplicate component record for each process plan that component was associated with. There are other fields, but this is enough for the question. When somebody looks up a Component (by selecting from a list), they should be able to see what Process Plans are associated with it. From there they will need to see the details of the selected Process Plan, including a listing of all Components that may be processed according to that plan. Each Process Plan can be for many Components, and each Component can be associated with many Process Plans, but I don't think this makes it a many-to-many relationship. it *is* a many-to-many relationship. to avoid the necessity for duplicate records in both tblProcessPlans and tblComponents, you need to remove the foreigns from each of those tables and put them in a third table, instead, as tblProcessPlanComponents PlanID (FK) (combo PK) ComponentID (FK) (combo PK) I can't imagine a field in a junction table that would not be in one of the other two tables. it doesn't matter if there are no additional fields required in the linking table. it's purpose is to link the other two tables together while avoiding record duplication. Instead, I see tblProcessPlan as the record source for frmProcessPlan, and tblComponents as the record source for a subform fsubComponents. whoa. you jumped right over the issue of table relationships and straight into forms. forget about forms, until you have modeled normalized tables that correctly store and link all your data appropriately. you can't use a form to fix a table design problem, and you'll go crazy trying. If I am still on track here, the Specifications are the next step. I think would have a Specifications table, with its PK a FK in tblProcessPlan. Each Process Plan can reference many Specifications, and each Specification can be associated with many Process Plans. again, a many-to-many relationship with the same issues noted above. and the same solution: use a linking table between process plans and specifications. As with Components, looking up a Specification would produce a listing of associated Process Plans. This is necessary because a revised Specification could mean revising the associated Process Plans. Am I heading in the right general direction with this? Any comments or suggestions? hth |
#3
|
|||
|
|||
Design questions
Thanks for the speedy reply. My comments are also inline.
-----Original Message----- comments inline: "Bruce" wrote in message ... I have successfully completed several smaller databases that work quite well. Now I am undertaking the largest project so far, and I have some design questions before I have completed the first table. My company performs a variety of manufacturing processes on a variety of Components. The process is detailed in a Process Plan, each of which is numbered in the format 04- 20 (for the twentieth plan developed in 2004). Each Process Plan can be for one or more Components. The same process on two different Components could be two separate Process Plans, or it could be the same Process Plan. Also, one component could be processed according to several different Process Plans. Also, each Process Plan references one or more Specifications, but probably no more than 5. Here is what I have so far, ignoring for a moment the Specifications: tblProcessPlan PlanID (PK) Component ID (FK) PlanNumber (could probably be the PK, but I'm not sure) Revision Date Process Archive (Y/N, for queries) so this allows you to link each specific process plan to a single specifc component. but how do you plan to link the same process plan to another specific component? in this two-table setup, you'd have to enter a duplicate process plan record for each component that plan was associated with. that solution violates table normalization rules. Actually, I am aware of normalization rules, but I am relatively inexperienced in DB design. I am aware that duplicate data entry is generally to be avoided, and as I continued to experiment with the design I saw that the two table setup would be a problem in that regard. tblComponent ComponentID (PK) PlanID (FK) ComponentNumber (it is possible that 2 manufacturers will use the same number, so no PK here) ComponentName again this allows you to link each specific component to a single specifc process plan. and again, you'd have to enter a duplicate component record for each process plan that component was associated with. Not good. There are other fields, but this is enough for the question. When somebody looks up a Component (by selecting from a list), they should be able to see what Process Plans are associated with it. From there they will need to see the details of the selected Process Plan, including a listing of all Components that may be processed according to that plan. Each Process Plan can be for many Components, and each Component can be associated with many Process Plans, but I don't think this makes it a many-to-many relationship. it *is* a many-to-many relationship. to avoid the necessity for duplicate records in both tblProcessPlans and tblComponents, you need to remove the foreigns from each of those tables and put them in a third table, instead, as tblProcessPlanComponents PlanID (FK) (combo PK) ComponentID (FK) (combo PK) Again, I see your point. I can't imagine a field in a junction table that would not be in one of the other two tables. it doesn't matter if there are no additional fields required in the linking table. it's purpose is to link the other two tables together while avoiding record duplication. I have designed one database that used a junction table that had a unique field. I fell into a trap of thinking the unique field justified use of the junction table, when all along it is the many-to-many that makes a junction table necessary. I appreciate your making the point, because I was a bit stuck there. Instead, I see tblProcessPlan as the record source for frmProcessPlan, and tblComponents as the record source for a subform fsubComponents. whoa. you jumped right over the issue of table relationships and straight into forms. forget about forms, until you have modeled normalized tables that correctly store and link all your data appropriately. you can't use a form to fix a table design problem, and you'll go crazy trying. I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. If I am still on track here, the Specifications are the next step. I think would have a Specifications table, with its PK a FK in tblProcessPlan. Each Process Plan can reference many Specifications, and each Specification can be associated with many Process Plans. again, a many-to-many relationship with the same issues noted above. and the same solution: use a linking table between process plans and specifications. As with Components, looking up a Specification would produce a listing of associated Process Plans. This is necessary because a revised Specification could mean revising the associated Process Plans. Am I heading in the right general direction with this? Any comments or suggestions? hth |
#4
|
|||
|
|||
Design questions
If I may 'butt-in" here and say that it's not clear that you've restructured your table design yet. If so, then I'd suggest that you post your new table design for tina to review.
-- rpw "Bruce" wrote: Thanks for the speedy reply. My comments are also inline. -----Original Message----- comments inline: "Bruce" wrote in message ... I have successfully completed several smaller databases that work quite well. Now I am undertaking the largest project so far, and I have some design questions before I have completed the first table. My company performs a variety of manufacturing processes on a variety of Components. The process is detailed in a Process Plan, each of which is numbered in the format 04- 20 (for the twentieth plan developed in 2004). Each Process Plan can be for one or more Components. The same process on two different Components could be two separate Process Plans, or it could be the same Process Plan. Also, one component could be processed according to several different Process Plans. Also, each Process Plan references one or more Specifications, but probably no more than 5. Here is what I have so far, ignoring for a moment the Specifications: tblProcessPlan PlanID (PK) Component ID (FK) PlanNumber (could probably be the PK, but I'm not sure) Revision Date Process Archive (Y/N, for queries) so this allows you to link each specific process plan to a single specifc component. but how do you plan to link the same process plan to another specific component? in this two-table setup, you'd have to enter a duplicate process plan record for each component that plan was associated with. that solution violates table normalization rules. Actually, I am aware of normalization rules, but I am relatively inexperienced in DB design. I am aware that duplicate data entry is generally to be avoided, and as I continued to experiment with the design I saw that the two table setup would be a problem in that regard. tblComponent ComponentID (PK) PlanID (FK) ComponentNumber (it is possible that 2 manufacturers will use the same number, so no PK here) ComponentName again this allows you to link each specific component to a single specifc process plan. and again, you'd have to enter a duplicate component record for each process plan that component was associated with. Not good. There are other fields, but this is enough for the question. When somebody looks up a Component (by selecting from a list), they should be able to see what Process Plans are associated with it. From there they will need to see the details of the selected Process Plan, including a listing of all Components that may be processed according to that plan. Each Process Plan can be for many Components, and each Component can be associated with many Process Plans, but I don't think this makes it a many-to-many relationship. it *is* a many-to-many relationship. to avoid the necessity for duplicate records in both tblProcessPlans and tblComponents, you need to remove the foreigns from each of those tables and put them in a third table, instead, as tblProcessPlanComponents PlanID (FK) (combo PK) ComponentID (FK) (combo PK) Again, I see your point. I can't imagine a field in a junction table that would not be in one of the other two tables. it doesn't matter if there are no additional fields required in the linking table. it's purpose is to link the other two tables together while avoiding record duplication. I have designed one database that used a junction table that had a unique field. I fell into a trap of thinking the unique field justified use of the junction table, when all along it is the many-to-many that makes a junction table necessary. I appreciate your making the point, because I was a bit stuck there. Instead, I see tblProcessPlan as the record source for frmProcessPlan, and tblComponents as the record source for a subform fsubComponents. whoa. you jumped right over the issue of table relationships and straight into forms. forget about forms, until you have modeled normalized tables that correctly store and link all your data appropriately. you can't use a form to fix a table design problem, and you'll go crazy trying. I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. If I am still on track here, the Specifications are the next step. I think would have a Specifications table, with its PK a FK in tblProcessPlan. Each Process Plan can reference many Specifications, and each Specification can be associated with many Process Plans. again, a many-to-many relationship with the same issues noted above. and the same solution: use a linking table between process plans and specifications. As with Components, looking up a Specification would produce a listing of associated Process Plans. This is necessary because a revised Specification could mean revising the associated Process Plans. Am I heading in the right general direction with this? Any comments or suggestions? hth |
#5
|
|||
|
|||
Design questions
I am not trying to solve a table problem with a form. I
didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth |
#6
|
|||
|
|||
Design questions
thx rwp - i thought it might be just *me* that it wasn't clear to. g
"rpw" wrote in message ... If I may 'butt-in" here and say that it's not clear that you've restructured your table design yet. If so, then I'd suggest that you post your new table design for tina to review. -- rpw "Bruce" wrote: Thanks for the speedy reply. My comments are also inline. -----Original Message----- comments inline: "Bruce" wrote in message ... I have successfully completed several smaller databases that work quite well. Now I am undertaking the largest project so far, and I have some design questions before I have completed the first table. My company performs a variety of manufacturing processes on a variety of Components. The process is detailed in a Process Plan, each of which is numbered in the format 04- 20 (for the twentieth plan developed in 2004). Each Process Plan can be for one or more Components. The same process on two different Components could be two separate Process Plans, or it could be the same Process Plan. Also, one component could be processed according to several different Process Plans. Also, each Process Plan references one or more Specifications, but probably no more than 5. Here is what I have so far, ignoring for a moment the Specifications: tblProcessPlan PlanID (PK) Component ID (FK) PlanNumber (could probably be the PK, but I'm not sure) Revision Date Process Archive (Y/N, for queries) so this allows you to link each specific process plan to a single specifc component. but how do you plan to link the same process plan to another specific component? in this two-table setup, you'd have to enter a duplicate process plan record for each component that plan was associated with. that solution violates table normalization rules. Actually, I am aware of normalization rules, but I am relatively inexperienced in DB design. I am aware that duplicate data entry is generally to be avoided, and as I continued to experiment with the design I saw that the two table setup would be a problem in that regard. tblComponent ComponentID (PK) PlanID (FK) ComponentNumber (it is possible that 2 manufacturers will use the same number, so no PK here) ComponentName again this allows you to link each specific component to a single specifc process plan. and again, you'd have to enter a duplicate component record for each process plan that component was associated with. Not good. There are other fields, but this is enough for the question. When somebody looks up a Component (by selecting from a list), they should be able to see what Process Plans are associated with it. From there they will need to see the details of the selected Process Plan, including a listing of all Components that may be processed according to that plan. Each Process Plan can be for many Components, and each Component can be associated with many Process Plans, but I don't think this makes it a many-to-many relationship. it *is* a many-to-many relationship. to avoid the necessity for duplicate records in both tblProcessPlans and tblComponents, you need to remove the foreigns from each of those tables and put them in a third table, instead, as tblProcessPlanComponents PlanID (FK) (combo PK) ComponentID (FK) (combo PK) Again, I see your point. I can't imagine a field in a junction table that would not be in one of the other two tables. it doesn't matter if there are no additional fields required in the linking table. it's purpose is to link the other two tables together while avoiding record duplication. I have designed one database that used a junction table that had a unique field. I fell into a trap of thinking the unique field justified use of the junction table, when all along it is the many-to-many that makes a junction table necessary. I appreciate your making the point, because I was a bit stuck there. Instead, I see tblProcessPlan as the record source for frmProcessPlan, and tblComponents as the record source for a subform fsubComponents. whoa. you jumped right over the issue of table relationships and straight into forms. forget about forms, until you have modeled normalized tables that correctly store and link all your data appropriately. you can't use a form to fix a table design problem, and you'll go crazy trying. I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. If I am still on track here, the Specifications are the next step. I think would have a Specifications table, with its PK a FK in tblProcessPlan. Each Process Plan can reference many Specifications, and each Specification can be associated with many Process Plans. again, a many-to-many relationship with the same issues noted above. and the same solution: use a linking table between process plans and specifications. As with Components, looking up a Specification would produce a listing of associated Process Plans. This is necessary because a revised Specification could mean revising the associated Process Plans. Am I heading in the right general direction with this? Any comments or suggestions? hth |
#7
|
|||
|
|||
Design questions
You are right, I did not specify the table structure.
Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . |
#8
|
|||
|
|||
Design questions
no, you got it right.
the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . |
#9
|
|||
|
|||
Design questions
The way this needs to work is for the Process Plan to show
all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . |
#10
|
|||
|
|||
Design questions
i'd say skip the query. instead, base the subform directly on
tblPartListing. base the combo box for Components solely on tblComponents. since you're including all three fields of tblComponents in the query's columns, those values are available to you to show in the subform record as you choose - which i think you already know. from the above setup, you should be able to add new components to the combo box "on the fly". if you still have trouble with it after making those changes, post the code from your combo box's NotInList event. if you need to do a sort on the subform, based on the part numbers....hmmm, you might try sorting on a calculated control whose control source is set to =ComboBoxName.Column(1) i've never tried to do a form sort on a calculated control - don't know if it'll work or not. hth "Bruce" wrote in message ... The way this needs to work is for the Process Plan to show all associated Components. A new Process Plan will start with a description of the plan, then will list Components. Most process plans are associated with multiple Components, but relatively few components are associated with multiple Process Plans (except in the case of revisions to existing Process Plans). It needs to be Process Plan first, then components. In answer to your question about the user adding to the combo box list, it is not only allowed, it is the whole point. I need to be able to add Component numbers and descriptions to tblComponents on the fly. I will enter the Process Plan information, then either select components from a combo box or type them into the box. For Process Plan 12345, I have Components 123, bolt; 124, nut; and 125, washer. Below the Process Plan description I need to see: 123 Bolt 124 Nut 125 Washer If I select Component number 123 from a combo box, "Bolt" needs to show up next to it. If Component number 124 is not in the list, I will add it right there on the subform, and will add "Nut" next to it. The next time I need to add 124 (to another Process Plan), it will appear on the combo box list. What I have managed so far is a mainform based on tblProcessPlan, and a subform based on qryPartListing, which combines tblComponent and tblPartListing (the junction table). The subform has a combo box (cboList) based on a select query, based in turn on tblComponent (the query is to allow sorting later). ComponentID is the first column, Component number is the second, and Component description is the third. The combo box has three columns; only the second is visible (widths of 1 and 3 are zero). The text box for Component description has as its Control Source the third column of the combo box: = [cboList].Column(2). This is fine as long as I am always selecting an exisiting Component, but it does not work on the fly. I don't know if the query as the source for the subform is the correct approach. I can't see another way to link to tblComponents. I really appreciate the time you have put into helping with this. -----Original Message----- no, you got it right. the form/subform you built is focused on adding/updating ProcessPlan records, including listing components that "belong to" that plan. you can also (or instead) build a form/subform with the opposite approach: mainform based on components, subform based on PartListing, foreign key from tblComponents automatically entered in subform, foreign key from tblProcessPlans entered via a combo box that is based on tblProcessPlans. in the above form, the focus is on add/updating Component records, including listing process plans that "belong to" that component. in either form/subform setup, my first question is: do you want the user to be able to add an entry to the combo box list? you have to decide whether the user will have enough info to make a complete and valid entry in the combo box's underlying table. if the answer to the question is Yes, it's fairly easy to set up an process to allow entries to be added to the underlying table (and populate the combo box droplist) on-the-fly. if you want to do that, and need help setting it up, post back and i'll provide a code sample. hth "Bruce" wrote in message ... You are right, I did not specify the table structure. Forget about specifications for now, by the way. I would like to limit this until I have a better handle on managing one junction table. The junction table's only unique field is its PK. The other fields are linked to tblProcessPlan and tblComponent: tblProcessPlan PlanID (PK) PlanNumber (our internal assigned number) Revision Process Date Archived (Y/N) tblComponent ComponentID (PK) ComponentNumber ComponentName tblPartListing ListingID (PK) PlanID (FK) ComponentID (FK) First, suppose I have a new Process Plan. I am not trying to get ahead of myself, nor to fix a table problem with a form. I understand that a properly normalized table structure is at the heart of a successful database. Having said that, at some point I like to use autoform to make forms to test things, because I find it easier to envision things that way than directly in a table. I made frmProcessPlan from tblProcessPlan. After entering the Plan number, date, etc. I would like to enter the component number or numbers. I expect this will occur in a subform based on tblPartListing (?). I am just not able to get my brain around how that will happen. (I have already populated tblComponents with a listing (imported from a spreadsheet) of part numbers and descriptions.) The parent/child links of the subform control are as you suggest, and a combo box on the subform has tblComponent as its row source. Its bound column is the PK from tblComponent, and its visible column is Column 2 (the Component number). However, I cannot add a component number that is not already in tblComponent, and I cannot add a Component description at all. Is there enough here for you to tell what I am doing wrong? I know that I am just not getting something basic. -----Original Message----- I am not trying to solve a table problem with a form. I didn't know there was a table problem. Now I have built the tables and their relationships. I have imported a list of Component numbers and descriptions into tblComponents, and am now trying to associate a Process Plan with certain Component numbers. I have created a form based on tblProcessPlan. After entering the plan number, date, etc. I want to associate certain Component numbers with that Process Plan. Maybe it's just because it is Friday afternoon, but I can't sort out how to do that. If I am not getting ahead of myself I want to begin experimenting with data entry. from the remarks in your post (including above excerpt), i'm not clear on whether you did implement the linking table for tblProcessPlans and tblComponents. and the linking table for tblProcessPlans and tblSpecifications. if you did set up both linking tables, then i'd suggest the following: you made a form based on tblProcessPlan. to associate specific components with a specific plan, add a subform to that main form, based on the linking table. the Master/Child links in the subform control's properties will be the key field from tblProcessPlan. it will automatically be added to each record you enter in the subform. so you only need to enter the key value from tblComponents in each record in the subform. suggest you make that control a combo box, with its' RowSource set to tblComponents. hth . . |
|
Thread Tools | |
Display Modes | |
|
|