Results 1 to 10 of 10
  1. #1
    jwaes is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7

    Question Many to many with a 'favorite'

    I have three tables.
    Products
    Suppliers
    ProductSuppliers (a many-to-many joining them)


    ProductId (PK)
    SupplierId (PK)
    Favorite (boolean)

    So a product can be ordered from multiple suppliers. (and suppliers can supply multiple products)
    But for a product we have one and only one favorite supplier.
    So for each ProductId, we can have multiple SupplierIds, but only One with a favorite set to TRUE.

    How can i enforce that?
    Last edited by jwaes; 05-15-2019 at 04:24 AM. Reason: solved

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If its the same favourite supplier for each product, set it as the default value
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    I guess your tables are filled through forms. I would add a code to the form where you register the ProductSuppliers records and add a routine that, when the user marks a supplier as favorite if there already exists a favorite for that combination; if so show a pop-up window that asks the user if they want to alter the favourite. If yes: uncheck the Favorite field in the existing combination and check the field for the new favorite.

  4. #4
    jwaes is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7
    No, not the same for each product

    Product
    - Apple
    - Pear

    Supplier
    - Gardenworld
    - Fruit Inc
    - Orchard

    ProductSuppliers
    - Apple - Gardenworld - favorite
    - Apple - Fruit Inc
    - Applce - Orchard
    - Pear - Fruit Inc
    - Pear - Orchard - favorite

    Hope this helps, to make it more clear
    We can order apples from three suppliers but for the moment Gardenworld is our favorite, the other two are 'fallback' if they cant deliver for example
    Pears we can order from 2 suppliers, but here for pears, Orchard is our favorite (because their price is substantially lower for example)

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    jwaes,

    An example with some data would be helpful.
    It seems you could check the availability of a Product at your "Favorite" supplier when making a PurchaseOrder, and, if not available --go to another Supplier, otherwise use "Favorite".

    A separate table of ProductFavSupplier may be appropriate.

    At first look it would seem that a little logic/coding should suffice.

    Good luck.

    ooops : I see other responses have been received while I was thinking/typing.

    Is your Favorite always the lowest Price Supplier?

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    tblProducts: ProductID, ProductName;
    tblSuppliers: SupplierID, SupplierName
    tblProductSuppliers: ProductSupplierID, ProductID, SupplierID, IsFavorite.

    As NoellaG did state, whenever you set some supplier as favorite for some product (IsFavorite = True), for all other suppliers of same product IsFavorite must be set to False.

    Whenever you register a purchase order, the value of control for supplier (probably a combobox) must be set equal to SupplierID which is favorite for this product (in case you specify product first of-course) - you can use AfterUpdate event of combo where you select product (but you have to check the supplier combo at first - when this is not empty, then previous selection there must remain unchanged).

  7. #7
    jwaes is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7
    @orange .. not necessarily the lowest price.
    @ArviLaanemets .. Ok, i was hoping i could do it with some sort of unique key or so, but it looks i ll have to code it. Thanks

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by jwaes View Post
    @orange .. not necessarily the lowest price.
    @ArviLaanemets .. Ok, i was hoping i could do it with some sort of unique key or so, but it looks i ll have to code it. Thanks
    Why code - to enforce a choice? I see 2 examples of suitable table design so you must not be referring to that. Consider a ranking rather than a simple boolean since you will have multiple supplier records for the same product, why not rank them as 1, 2 or 3 so that user knows which is next in line if #1 can't provide?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think you're going to have to have at least one query running in the background but I'd ask a further question.

    Is each product REQUIRED to have a 'favored' vendor? In other words can apples have no favored vendor? Do you need to enforce that?

    Enclosed is a simple example of how you would do it with an unbound form.

    Bound forms are a little trickier in that you have to use the BEFORE UPDATE or BEFORE INSERT event to prevent bad data from getting into your table. Do not get hung up on the code I use what you really need to pay attention to the code I have highlighted in the ON CLICK event of the Add/Update button.

    jwaes.zip

  10. #10
    jwaes is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    7
    Thank you for the work you put into this. Works like a charm.

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

Similar Threads

  1. Replies: 5
    Last Post: 06-12-2018, 01:35 PM
  2. Replies: 3
    Last Post: 11-06-2015, 05:06 PM

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