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?

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

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

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

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

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

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



.

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



.

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



.



.

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



.



.