Design questions

Discussion in 'Access Table Design' started by Bruce, Jun 11, 2004.

  1. Bruce

    Bruce Guest

    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

    PlanID (PK)
    Component ID (FK)
    PlanNumber (could probably be the PK, but I'm not sure)
    Archive (Y/N, for queries)

    ComponentID (PK)
    PlanID (FK)
    ComponentNumber (it is possible that 2 manufacturers
    will use the same number, so no PK here)

    There are other fields, but this is enough for the
    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
    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
    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, Jun 11, 2004
    1. Advertisements

  2. Bruce

    tina Guest

    comments inline:

    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.

    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.

    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,

    PlanID (FK) (combo PK)
    ComponentID (FK) (combo PK)
    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.

    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.

    again, a many-to-many relationship with the same issues noted above. and the
    same solution: use a linking table between process plans and
    tina, Jun 11, 2004
    1. Advertisements

  3. Bruce

    Bruce Guest

    Thanks for the speedy reply. My comments are also inline.
    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.
    Not good.
    Again, I see your point.
    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.
    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.
    Bruce, Jun 11, 2004
  4. Bruce

    rpw Guest

    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, Jun 12, 2004
  5. Bruce

    tina Guest

    I am not trying to solve a table problem with a form. I
    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

    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.

    tina, Jun 12, 2004
  6. Bruce

    tina Guest

    thx rwp - i thought it might be just *me* that it wasn't clear to. <g>

    restructured your table design yet. If so, then I'd suggest that you post
    your new table design for tina to review.
    tina, Jun 12, 2004
  7. Bruce

    Bruce Guest

    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:

    PlanID (PK)
    PlanNumber (our internal assigned number)
    Archived (Y/N)

    ComponentID (PK)

    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.
    Bruce, Jun 14, 2004
  8. Bruce

    tina Guest

    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.

    tina, Jun 14, 2004
  9. Bruce

    Bruce Guest

    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
    I really appreciate the time you have put into helping
    with this.
    Bruce, Jun 15, 2004
  10. Bruce

    tina Guest

    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
    i've never tried to do a form sort on a calculated control - don't know if
    it'll work or not.


    tina, Jun 15, 2004
  11. Bruce

    Bruce Guest

    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. Therefore, it seems that
    the control source for a combo box based on tblComponent
    can only be an expression:
    =[tblComponent]![ComponentNumber] Column 1 is the PK, 2
    is ComponentNumber, 3 is ComponentDescription; I tried
    both 1 and 2 as the bound column. 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.
    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
    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.
    Bruce, Jun 16, 2004
  12. Bruce

    tina Guest

    stick with this just a little longer. comments inline:

    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

    Therefore, it seems that
    see above.

    Column 1 is the PK, 2
    set the combo box BoundColumn to 1. the primary key (ComponentID) is the
    value you need to save.

    However, any attempt
    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.

    good. that makes it even easier - no complications involving sorting the
    subform records.

    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>


    tina, Jun 16, 2004
  13. Bruce

    Bruce Guest

    Thanks again for all of your patience in the midst of my
    frustration. I was indeed making the mistake of trying to
    set the control source of the combo box to the visible
    value rather than to the PK. As you explained, I need to
    set the combo box control source to the PK from the
    junction table, and the row source to the table (by way of
    a SQL statement). Component description is drawn from
    combo box.Column(2) as I mentioned earlier. I have the
    Not In List event set to hop over to a form for editing
    the list of part numbers.
    I have lots more to do on this database. I hope you won't
    duck the next time you see my name come up in the
    newsgroup. Your help has been invaluable.
    ComponentNumber. your combo box
    Bruce, Jun 16, 2004
  14. Bruce

    tina Guest

    no problem, and you're very welcome. the tough ones just make me that much
    happier when it finally works! :)

    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
    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.

    tina, Jun 17, 2004
  15. Bruce

    Bruce Guest

    LIteral column 1 , or Column(0) in AccessSpeak is the PK;
    literal column 2, or Column(1) is the Component Number;
    and literal column 3, or Column(2) is the description. My
    Combo box is three columns: Literal column 1, or Column
    (0) is the bound column, and column widths are 0";1";0".
    When I select the component number, the Description text
    box swipes the invisible literal third column, or Column
    (2). I had encountered that curiosity on an earlier
    project. It does make it difficult to talk about the
    columns. You certainly gave my posting a very thorough
    reading to pick up on that. By the way, I have begun to
    enter test data into the DB, and all is working as hoped.
    Bruce, Jun 17, 2004
  16. Bruce

    tina Guest

    oh good, glad it's working for you. :)
    re the combo box columns: i overkill sometimes, telling people what they
    already know - but better safe than sorry! <g>

    tina, Jun 19, 2004
  17. Bruce


    i have issue in pdf design...
    pls help me..
    thank you
    , Apr 12, 2013
  18. Are you designing pdf files in a Microsoft Access database application? That's
    the subject of this (defunct) newsgroup.

    I strongly suspect you need to a) choose a more appropriate forum, perhaps one
    having to do with designing pdf files; and b) post a longer and clearer
    question. This one sounds like "Doctor, I don't feel good, what should I take"
    - asked in a bicycle repair shop!

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, Apr 12, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.