Results 1 to 6 of 6

Thread: SubForms

  1. #1
    Join Date
    Oct 2008

    Unanswered: SubForms

    I have created a part number database. I have a form that allows you specify a part. I would like to have sub forms that allow me to select multiply manufacturer. Each Manufacturer could have several distributors. I have set up a table that contains Manufactures and Distributors. I then have query that separates the distributors from manufacturers. I did this because the data is the same for distributor and manufacturers.

    On the component (my part) form, I would like a sub form that shows in datasheet view multiply manufactures. Then a sub form under manufacturer for distributors.

    How do I define the relationships and set up the sub forms such that one component can have multiple manufactures and each manufacturer has multiple distributors.
    Do I need to include any fields any of the tables to cross-reference.

    1 uF Ceramic Capacitor 6.3V
    AVX Corporation P/N - 06036D106MAT2A
    Digi-Key P/N 478-5318-2-ND
    Mouser P/N 581-06036D106MAT2A
    Panasonic ECG P/N ECJ-1VB0J106M
    Digi-Key P/N PCC2395TR-ND
    Mouser P/N GRM188R60J106ME47D
    Last edited by rogerdalebrown; 11-16-08 at 21:09.

  2. #2
    Join Date
    Nov 2008
    It seems complex enough, so first you have to make a complete diagram on the paper before actually begin to build the tables, and don't mind about the forms before completing the tables and their relationships... Correct me if I am wrong but for the two first tables 'Parts' and 'Manufacturers' I think we have a many-to-many relationship here, because a part can be produced by more than one manufacturer but also a manufacturer can produce more than one part. This relationship can be obtained by using an intermediate table that will connect any part with any manufacturer. The tables must be as follows (with asterisks I have marked the primary keys):

    Table 'Parts':

    Table 'Manufacturers':

    Table 'connect_Part_Manufacturer':

    The relationships are
    connect_Part_Manufacturer 1--->00 Parts
    connect_Part_Manufacturer 1--->00 Manufacturers
    Last edited by SocratesF; 11-18-08 at 11:11.

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    Table 'connect_Part_Manufacturer':
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2008

    The part can have many manufactures. Each Manufacture can have many distributors,

    Part A1
    Manufacture 123 part numbe XXXX
    Distributor abc Partnumber ZZZZ
    Disctributor XYZ Part Number wwww
    Manufacturer 789 partnumber 99999
    Distributor abc partnumber 9xxx9xx
    Distributor XYZ partnumber 88888xx8

    So you see each of my partnumbers is 1 to many manufactureres
    THis manufacturer has a part number that corresponds to my part number and has several distributors. Manufacturere to distributor is 1 to many

    Distributor XYZ

  5. #5
    Join Date
    Dec 2004
    So the question you want to answer is "who do I call when I need a 1 uF Ceramic Capacitor 6.3V?"

    Your part number A123456 can be manufacturer A's p/n ABC-1234 or manufacturer B's p/n 5678-DEF. The bin for this part can contain a mixture of components from different places, but they all perform a similar function.

    Distributor Y carries parts from manufacturer A. Distributor X carries parts from manufacturer B. Distributor W carries parts from both manufacturers.

    So you need a table for your part numbers, a table for manufacturers, and table for distributors. You've put your distributors and manufacturers in the same table and separate them with a query - it's much simpler to have two tables.

    You'll also need a table to connect your part number to the manufacturer part number and a table to connect the distibutor to the manufacturer.

    Nobody cares how the distributor lists the part - so you don't need to worry about their part number. If I'm a distributor for that manufacturer, I'll find that part in my inventory.

    Primary Keys marked with asterisks.

    Table "Parts"
    *PartNo, PartDescription
    A1, 1 UF Ceramic Capacitor

    Table "Manufacturers"
    *ManufacturerID, ManufacturerName
    A, AVX Corp
    B, Panasonic

    Table "Distributors"
    *DistributorID, DistributorName
    Y, Digi-Key
    X, Mouser
    W, Mom's Electronics

    Table "Parts_Manufacturers"
    *PartNo, *ManufacturerID, *ManufacturerPartNo
    A1, A, 06036D106MAT2A
    A1, B, ECJ-1VB0J106M

    Table "Distributors_Manufacturers"
    *DistributorID, *ManufacturerID
    Y, A
    X, B
    W, A
    W, B

    Each part can be made by several different manufacturers and be supplied by many distributors.

    You can build either a query or a set of forms and subforms to display the results.

  6. #6
    Join Date
    Oct 2008
    I see said the blind man.

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts