Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2008
    Posts
    10

    Question Unanswered: New to Access - Need help with Supplier/Product/Order Database.

    Hi, I'm creating a database which allows the user to make and store new orders. Some Products have more than one Supplier, and some Suppliers have more than one Product. I need to be able to select the Product I need and then select the cheapest Supplier of that product. I've been tryng for ages to get it right but I can't, and I'm not sure whether it's my tables, relationships or my form building, but I'm stuck.

    Here's a screen shot of my tables/relationships: http://i35.tinypic.com/52kj07.jpg


    Any help would be great. Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why is SupplierProductPricesId present in the Order table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2008
    Posts
    10
    Well it wasn't, but then I thought, how will I know which supplier the order was made from? So I figured if I put that there, it would provide the product and supplier...But I'm guessing it shouldn't be there?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It should only be there if you will only ever order one product at a time. The OrderInfo table looks like the detail record for the info. If you drop the ProductId from there and add your SupplierProductPricesId instead, you will be able to use joins to get all of the other information you need.

    As it is, you've introduced a funky circular reference that doesn't quite answer the questions you intend to ask.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2008
    Posts
    10
    Ah right okay. So SupplierProductPrices should be in OrderInfo, not Order, and ProductID should not be in OrderInfo? So SupplierProductPrices is related to OrderInfo and Order to OrderInfo?

    Lol I know my relationships look dodgy, it's because I keep trying new things in the hope it'll work

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ayup .
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2008
    Posts
    10
    Great, thanks alot! What sort of layout would I need for the form? Would OrderID be on the main form and then I'd have OrderInfo and SupplierProductPrices Subforms?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I have no clue, that's up to you and your business requirements.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Oct 2008
    Posts
    10
    I have Order as the Main Form, and OrderInfo as the subform, but with OrderInfo, I have to select a SupplerProductPricesID (primary key) to select a Product with the cheapest Supplier...How will the end user know which number to choose? Is there a way of selecting ProductName, and that in turn, filters a SupplierName combo box? thanks

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The combo box should have two columns, the ID and the product name in it's Row Source, the Column Count should be 2, the Bound Column should be 1 and the Column Widths should be 0;5. This way, the user sees and selects ProductName but the combo box still holds the primary key.
    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

Posting Permissions

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