Results 1 to 3 of 3
  1. #1
    fftempest is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2011
    Posts
    2

    Help with database design Many-to-Many and data entry

    I am trying to develop a database but I'm not sure if I have it designed correctly.
    So far I have 4 tables.

    tblBOM
    - kNum (primary key, autonumber)
    - BOM_Num (text)
    - Rev (text)
    - Rev_Date (short date)
    - Comments (text)

    tblJunction_BOM_Comp
    - kNum (primary key, autonumber)
    - BOM_FK (long int)
    - Comp_FK (long int)

    tblComponents
    - kNum (primary key, autonumber)
    - PartNumber (text)
    - Description (text)
    - Value (text)
    - Comments (text)

    tblRefDes
    - kNum (primary key, autonumber)
    - RefDes (text)
    - Comments (text)
    - RefDes_id (long int).

    I have the following relationships set.
    One-to-Many from tblBOM (primary key) to tblJunction_BOM_Comp (BOM_FK).
    One-to-Many from tblComponents (primary key) to tblJunction_BOM_Comp (Comp_FK).
    (The above 2 relationships gives me a Many-to-Many relationship between tblBOM and tblComponents.)
    One-to-Many from tblComponents (primary Key) to tblRefDes (RefDes_id).

    What I am striving for is to make a BOM for widget X (BOM number 1-A) that will contain Component PartNumber 101-001 (a capacitor) and
    have multiple RefDes of C1, C2, C3 and C4. My current design will accomodate that. Now I want to add another BOM for widget Y (BOM
    number 1-B) that will contain the same Component PartNumber 101-001 but now the component will have RefDes of C9 and C17. This is where
    I can't get my thinking straight.

    Do I need to modify the design of my database and add another Many-to-Many relationship between tblRefDes and tblBOM or between tblRefDes
    and tblComponents?

    Also, when I create a data entry form I would like to have displayed on the main form tblBOM.BOM_Num, tblBOM.Rev, tblBOM.Rev_Date, and
    tblBOM_Comments. Then in a subform (from tblComponents - datasheet style) I want to display tblComponents.PartNumber,
    tblComponents.Value and tblComponents.Description. I want to have the field tblComponents.PartNumber as a list box so when I hit the
    dropdown it will display all of the components (which are already in the database) and when I select one of them it will populate all of
    the other fields in the subform. I'm not quite sure how to do this. Also, I want to have a subform (from tblRefDes - datasheet style)
    below the subform (from tblComponents). That subform would just contain one field - tblRefDes.RefDes and then I could enter in multiple
    records of C1, C2, etc.



    I know how to create forms with subforms with subforms, but only with One-to-Many relationships between each of them. I don't know how
    to do it with Many-to-Many relationships (especially with a list box / datasheet style).
    So I figure I first need to get my tables/relationships figured out and then move on to the forms.
    I have been searhing forums and doing trial and error for so long now I just wind up going off on tangents.
    Kind of like searching the internet for replacement parts for the broken lawn mower and wind up watching videos of raccoons stealing
    peoples trash, then you wonder, how did I get here?

    If anyone can help it would be greatly appreciated.

  2. #2
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    As far as I can tell, you've got the many-to-many handled. I'm unclear about the purpose of tblRefDes. Would you please describe, in plain English, its place in the whole scheme, what it contains, and how it is supposed to relate to the other tables?

    Thanks,
    Steve

  3. #3
    fftempest is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2011
    Posts
    2
    Thank you for your response. I have figured out my dilemma, but I will give some more info. The RefDes is the designation of a part on an electronic board (ie C1, U2, R5). That designation along with the component (part) that is installed is unique to each BOM. However there are many BOM's, each with it's own RefDes / Component (part). So what I did was a little redesign and now I have one junction table with foreign keys pointing to the other 3 tables (tblRefDes, tblComponents, and tblBOM). As for the data entry, I have also figured that out but (to me) it's very complicated and it would probably take me at least 4 hours to refigure it out and write it down.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  2. Help starting up a daily entry database
    By sparx in forum Database Design
    Replies: 1
    Last Post: 01-12-2011, 10:56 AM
  3. Database Design / Data Entry
    By benjammin' in forum Access
    Replies: 1
    Last Post: 12-30-2010, 09:35 PM
  4. Database Design
    By accessprogram in forum Database Design
    Replies: 1
    Last Post: 12-05-2010, 12:02 AM
  5. Data Entry And Data Recalling
    By GeeDee in forum Access
    Replies: 0
    Last Post: 05-01-2007, 05:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums