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?
"Bruce" <anon...@discussions.microsoft.com> wrote in message
news:1b3f001c44fc8$3f7a9c30$a501...@phx.gbl...
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
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.
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.
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
"rpw" <r...@discussions.microsoft.com> wrote in message
news:4BFC946D-A9C2-4A0D...@microsoft.com...
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.
>.
>
hth
"Bruce" <anon...@discussions.microsoft.com> wrote in message
news:1c7cf01c4524d$04f48670$a001...@phx.gbl...
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" <anon...@discussions.microsoft.com> wrote in message
news:1ce5701c45311$66d45f40$a401...@phx.gbl...
"Bruce" <anon...@discussions.microsoft.com> wrote in message
news:1d3d801c4539f$6c43e1b0$a401...@phx.gbl...
> If the subform is based directly on (has as its record
> source) tblPartListing (the junction table) which contains
> only its own PK plus the FKs from tblComponents and
> tblProcessPlan, then ComponentNumber is not available as
> the combo box's control source.
correct. and it's *not supposed to be*. the foreign key field in
tblPartListing is ComponentID, NOT ComponentNumber. the value you need to
save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box
needs to be bound to (have it's ControlSource set to) ComponentID, NOT
ComponentNumber.
Therefore, it seems that
> the control source for a combo box based on tblComponent
> can only be an expression:
> =[tblComponent]![ComponentNumber]
see above.
Column 1 is the PK, 2
> is ComponentNumber, 3 is ComponentDescription; I tried
> both 1 and 2 as the bound column.
set the combo box BoundColumn to 1. the primary key (ComponentID) is the
value you need to save.
However, any attempt
> to select from the list or to add a part number results in
> the message (on the status bar) that the control can't be
> edited because it is bound to an expression. Therefore,
> the Not In List event cannot run.
once you set up the combo box the way i said, the user will *see* the
ComponentNumber in the combo box (because you have the first column's width
set to zero, which is correct). when he/she enters a component number that
does not exist in tblComponents, the NotInList event *will* run.
> By the way, the sorting I need to do is in the combo box's
> row source, which is easy enough in a SQL expression. I
> can sort the Component numbers that are associated with a
> Process Plan in a report as needed. I was not clear about
> that.
good. that makes it even easier - no complications involving sorting the
subform records.
> Thanks again for taking the time to reply, but it is clear
> that I am not explaining something properly. I will need
> to do more research.
hopefully, you'll come back to the thread and read this reply. you've
explained your setup and what you're doing very well. it seems that i'm the
one who has not been explaining something properly, because i haven't been
able to help you understand how i'm instructing you to set up the
form/subform/combobox solution - which is a standard solution, by the way,
not some crazy thing i made up. <g>
hth
one minor note: from previous descriptions, your combo box is 3 columns
altogether (though not all showing in the droplist, of course). the first
column being the primary key ComponentID, and the second column being
ComponentNumber. i noticed you referring to the second column as
ComboBox.Column(2).
just an fyi - columns in a combo box are zero-based. so if you want to refer
to column one, in code or in an expression, it would be
ComboBoxName.Column(0). to refer to column two, it's ComboBoxName.Column(1).
etc, etc.
"Bruce" <anon...@discussions.microsoft.com> wrote in message
news:1d64701c453d9$d2f3acd0$a101...@phx.gbl...
"Bruce" <anon...@discussions.microsoft.com> wrote in message
news:1db9501c454a6$08fe62d0$a301...@phx.gbl...