Results 1 to 9 of 9
  1. #1
    Lincoln is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    21

    Post Entering multiple parts onto an order form at once...

    Hi all

    I am setting up a database to which will help me order parts from several suppliers into an orderform/s etc

    My first problem:
    I wish to set up several 'Kits' which will be made up of several parts - when I select a 'Kit' I want all the items of the 'Kit' to be entered into the orderform seperately.



    Secondly:
    The parts of the Kits that I am making up are to be ordered from several suppliers - thus several orders need to be produced from one 'Kit'/selection.

    So how do I set up my tables etc to make all this happen?

    Can this be done in Access - I love this program - exciting!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    What you describe can be done but it will take some append queries and more than likely some Visual Basic for Application code to accomplish.

    But first and foremost is the table structure. The table structure is critical to any successful relational database application. So, what does your table structure currently look like?

  3. #3
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I'd start off with 4 tables:
    tblParts
    PartID
    PartDesc
    SupplierID

    tblSupplier
    SupplierID
    SupplierName

    tblKit
    KitID
    KitDesc

    tblKitPart
    KitPartID
    KitID
    PartID


    Bold = Table Name
    Underline = Primary Key (autonumber unless you have another plan for it)
    Italics = Foreign Key

    What will make it work is tblKitParts that facilitates a many to many relationship between Kit and Parts. lets say KitA contains PartA, PartB, and PartC. You would have 3 records
    A | PartA
    A | PartB
    A | PartC

    Now you can query for Kit A and you have a record for each part. All you have to do now is bring in tblParts and tblSupplier and you should be ready to fill out orders.

  4. #4
    Lincoln is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    21
    Theshabz thank you - just what I am needing - awesome. I will try and set this up as you mention - I am new to this and your help is very much appreciated
    thank you
    PS. will keep you posted to the outcome - may take me some time though.

  5. #5
    Lincoln is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    21
    Jzwp11 thank you, I will try Theshabz's suggestion and will update with outcome, thanks again.

  6. #6
    Lincoln is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    21
    Theshabz there is one more thing I didnt mention... that each part of each Kit will have eg 3 x A, 10 x B, 2 x C each from different suppliers - will this be a problem?

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would just need to add a quantity field to this table

    tblKitPart
    KitPartID
    KitID
    PartID
    Qty

    It doesn't matter which suppliers are involved since that the supplier is tied directly to the individual parts

  8. #8
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    "each from different suppliers" is the part that somewhat concerns me. Are you getting the same part from different suppliers? That changes the whole table structure.

  9. #9
    Lincoln is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    21
    Theshabz it worked! and I am very grateful - thank you!

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

Similar Threads

  1. Replies: 15
    Last Post: 10-01-2015, 10:06 AM
  2. Multiple products in an order
    By andeekaii in forum Access
    Replies: 3
    Last Post: 05-31-2011, 07:36 AM
  3. Replies: 1
    Last Post: 05-30-2011, 09:38 AM
  4. Need Direction Parts Form
    By Deano in forum Forms
    Replies: 2
    Last Post: 01-22-2011, 06:01 AM
  5. Accessing different parts of the same form
    By Lxmanager in forum Forms
    Replies: 1
    Last Post: 10-21-2010, 04:52 PM

Tags for this Thread

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