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