Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2012
    Posts
    7

    Question Unanswered: Lookup values based on a junction table

    Hello!
    I googled this question multiple times but didnt find anything (or didnt understand what I found)
    So could someone please help?
    I have a many to many relationship using junction table as follows:

    PRODUCTS
    pk - ID
    Source companies
    ...bunch of stuff

    JUNCTION
    pk(composite) - ProductID
    - CompanyID

    COMPANIES
    pk - CompanyID

    I need to create a lookup for the Source companies field in the first table, but I want a dropdown menu to have only Companies that have this product available based on a junction table. Is that possible? What would be the row source for such a column?
    Thank you very much in advance

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, it's possible. However, it's questionable WHY you want to do this, as (I think) it violates normalisation rules.

    If you need to denote a given supplier of an item as the default supplier (the only reason that I can imagine for having Company information against a product), you would be better off handling this with a Boolean field in the intersection table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    terminology rears its ugly head, in data modelling terms this is more usually refrerred to as an intersection table as opposed to a junction table, but who cares

    seems fine to me.. it'll be a pig to maintain over time

    you'd probably also want to add data relevant to that intersection of product and company, eg the suppliers part number, possibly the suppliers description
    heck you may even need a a sub table off the intersection table detailing cost prices.

    bear in mind that internally the product may have its own part numbers, and the suppliers likewise so there will be data which is internal (for the parts), and there is data that is relevant to the outside world (such as a supplier part number, or minimum order quantities and so on)

    eg you may be a wholesaler selling fasteners. you sell them according toyour own intrnla part number but you may source from several suppliers
    supplier A may be cheaper but may impose specific terms and conditions (eg MoQ, longer longer lead times, minimum order value and so on, Supplier B may be more expensive, but doenst' impose other terms. SUpplier C may be a pareto style supplier (they only stock the top selling items, they may only stock these items periodically), each of thoise suppliers may use their own internal part number and some may have their own product description. inside your system you don't care where the product came from, nor do you care what the suppliers call it. but you do need as mechanism that identifies that Supplier A's Right handed frulap is the same as supplier B's verstuken, but your company knows it as a M5 Stainless bolt, hexhead.........
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by weejas View Post
    Yes, it's possible. However, it's questionable WHY you want to do this, as (I think) it violates normalisation rules.

    If you need to denote a given supplier of an item as the default supplier (the only reason that I can imagine for having Company information against a product), you would be better off handling this with a Boolean field in the intersection table.
    Well yes, probably, I need to denote which supplier this item was actually procured from, and also know where else it is available
    So You propose to just add a bool to this same junction table? So to know which supplier is default I would need a query which lists only those who have it available AND who have YES in that field? But then again I could ruin the whole thing with misplaced tick and have 2 defaults, which is completely unwanted, any way to design it so that there was no way to make that error?
    Thank you very much, would probably go with this.

    But out of curiosty: how would I do the original design I asked? I reckon I would need some type of query to look up from, but everything I tried either lists them all or lists none of them

    Quote Originally Posted by healdem View Post
    terminology rears its ugly head, in data modelling terms this is more usually refrerred to as an intersection table as opposed to a junction table, but who cares

    seems fine to me.. it'll be a pig to maintain over time

    you'd probably also want to add data relevant to that intersection of product and company, eg the suppliers part number, possibly the suppliers description
    heck you may even need a a sub table off the intersection table detailing cost prices.


    bear in mind that internally the product may have its own part numbers, and the suppliers likewise so there will be data which is internal (for the parts), and there is data that is relevant to the outside world (such as a supplier part number, or minimum order quantities and so on)
    Its a good call, but its not planned for now, everything is internal.
    Thank you
    Last edited by gear; 12-17-12 at 20:50.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Okay, backtrack a stage here. You're confusing product setup with stock control.

    If you need to record who has supplied a given product, you need to do that in a stock control table. This will include the product number and supplier, but will also include things like dates ordered and received, expiry date (where relevant) and lot number (where relevant). If you need to record serial numbers for products, you can maintain a separate intersection table of part numbers, lot numbers and serial numbers.

    Going down the route of recording the originating supplier for a product in the product/supplier intersection table will lead to problems:
    • Every time you log products in, you will need to update this value.
    • What happens when you buy 100 products from supplier 1, use 50 of them and then buy another 75 from supplier 2? Which supplier do you flag as the source?

    In product setup, by all means maintain a default supplier flag. The source supplier for a given batch of products belongs in stock control.

    In terms of a SQL statement for a combo box to select the default supplier number, have a play with the following air code:
    Code:
    SELECT
      s.SupplierName
    , i.SupplierNumber
    FROM
      tblProductSupplier AS i
    INNER JOIN
      tblSuppliers AS s
    ON
      i.SupplierNumber = s.SupplierNumber
    WHERE
      i.ProductNumber = [txtProductNumber];
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    tracking which supplier supplied which product if their are multiple suppliers is tricky.
    to do that you need to know how much of a paprticular product has supplied and what has happened to that product over time (ie customer sales and returns, supplier deliveries and returns, wastage, damage, theft and so on). unless your organisation uses separate physical bin locations you have to apply some stock rule, whether thats FIFO, LIFO or whatever is up to you.

    if 'all' you want to do is note which supplier was the last supplier then that is an attribute of the intersection table. intersection tables don't have to be 'just' the primary keys of the parent tables it can store other data that is relevant to that intersection.

    if you have as has been proposed a boolean column in the intersection table indicating that this supplier is the last supplier. you'd need to clear the boolean flag on other suppliers for this product. when you come to populate your combo box then you sort in LastSupplierFlag & SupplierName order

    forget if true comes before or after false so it may need to be
    LastSupplierFlag & SupplierName DESC order
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2012
    Posts
    7
    Quote Originally Posted by weejas View Post
    Okay, backtrack a stage here. You're confusing product setup with stock control.

    If you need to record who has supplied a given product, you need to do that in a stock control table. This will include the product number and supplier, but will also include things like dates ordered and received, expiry date (where relevant) and lot number (where relevant). If you need to record serial numbers for products, you can maintain a separate intersection table of part numbers, lot numbers and serial numbers.

    Going down the route of recording the originating supplier for a product in the product/supplier intersection table will lead to problems:
    • Every time you log products in, you will need to update this value.
    • What happens when you buy 100 products from supplier 1, use 50 of them and then buy another 75 from supplier 2? Which supplier do you flag as the source?

    In product setup, by all means maintain a default supplier flag. The source supplier for a given batch of products belongs in stock control.

    In terms of a SQL statement for a combo box to select the default supplier number, have a play with the following air code:
    Code:
    SELECT
      s.SupplierName
    , i.SupplierNumber
    FROM
      tblProductSupplier AS i
    INNER JOIN
      tblSuppliers AS s
    ON
      i.SupplierNumber = s.SupplierNumber
    WHERE
      i.ProductNumber = [txtProductNumber];
    Thanks for that answer, but for the purposes of this db stock table would be overcomplication I'll look into that SQL

    Quote Originally Posted by healdem View Post
    if 'all' you want to do is note which supplier was the last supplier then that is an attribute of the intersection table. intersection tables don't have to be 'just' the primary keys of the parent tables it can store other data that is relevant to that intersection.

    if you have as has been proposed a boolean column in the intersection table indicating that this supplier is the last supplier. you'd need to clear the boolean flag on other suppliers for this product. when you come to populate your combo box then you sort in LastSupplierFlag & SupplierName order

    forget if true comes before or after false so it may need to be
    LastSupplierFlag & SupplierName DESC order
    Yes, exactly what I'm trying to achieve and what I'm trying to automate. Is there a way around managing all those flags with some kind of "SetDefaultSupplierForItem" query? This type of info is more suited for a radio button than a checkbox, if you know what I mean

    Thank you both for helping me to advance with this

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on how many suppliers you have ( or might have).
    Unless your suppliers were fixed in perpetuity I think you will have a problem with using the checkbox for this
    the real problem is if the number of suppliers per product changes over time you'd either have to set a default theoreticla maximum now, or do some fancy trickery creating the number checkboxes on the fly.. its doable, but its messy

    If it were me I'd probably implement this as a sub form embedded in the product form. I'd probably store the date the part was last ordered (in place of a lastsupplied flag, thatr way round you have know the last time you bought from each supplier. I'd order the sub form in supplydate DESCending order. that way round there is no pesky flag state to maintain. your users set a new date when they order from a supplier
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2012
    Posts
    7
    It seems I rectified every problem I asked about in different ways, but there's still a question about dat sql:
    Quote Originally Posted by weejas View Post
    Code:
    SELECT
      s.SupplierName
    , i.SupplierNumber
    FROM
      tblProductSupplier AS i
    INNER JOIN
      tblSuppliers AS s
    ON
      i.SupplierNumber = s.SupplierNumber
    WHERE
      i.ProductNumber = [txtProductNumber];
    If I use this query as a row source for one of the drop-down fields, how do I get it to know that [txtProductNumber] should be taken from data in a column in the same table? I'm not sure how to express it, sorry if I was not clear

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    That is what the WHERE clause is for. [txtProductNumber] is the name of the field on the form that holds the product number. If you've called this field something else, then use that name instead.

    The form works on the basis that you're only dealing with one record at a time, and so this code says, "Pick up the supplier name and number from the intersection of the suppliers table with the suppliers/product intersection table, joined on supplier number, where the item number matches the one on this record". By the way, if your product number is actually a text value, you will need to delimit it with apostrophes:
    Code:
    WHERE
       i.ProductNumber = '[txtProductNumber]';
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Dec 2012
    Posts
    7
    Yeah, for the form.. does the same SQL apply if I want to put it in a row source of a table itself?

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    O.o

    Tables don't have row sources...

    That SQL statement was designed specifically to populate a combo box. Nothing else.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  13. #13
    Join Date
    Dec 2012
    Posts
    7
    So.. In a table there is no way to select supplier only from suppliers who have the item available, not all suppliers? (provided those who have it available are defined in a junction table?)
    Thats what the problem is all about, I need a lookup field "DefaultSupplier" in a table of Products "filtered" to contain only those suppliers WHERE Junction.ProductID=Products.ID
    So for each row it would be a different list, is that possible?

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you are not getting to grip[s with what relational databases are about:-

    in the relational database world you have:-
    essentially two primary things
    Tables which store data
    Queries which manipulate data
    .. a query can appear as a many things, you can:-
    do admin (ie manipulate the table structure create, delete modify table/column/index design.. so called DDL)
    manipulate data using SELECT, INSERT,DELETE and UPDATE

    what tools like Access do is overlay those basic features with a user interface usually as either a screen viewpoint (a form) or a printed viewpoint (a report)

    there is no way within a table to select a supplier.. its not possible. there is the bastardised lookup wizard which I think may have changed recently but historically has been a thing to avoid but even that does not do what you want.

    I'm expecting that you are using a form to do the CRUD work on your products
    I'm expecting that somewhere on that form you want a mechanism to identify difrerent suppliers for a specific product. not that long ago you wanted to use radio or check boxes. those are (primarily) form objects.
    How you represent your suppliers for a product depends very much on how your data is stored (ie the SuppliersProducts table) designed)

    as said before my view would be to have ProductID, SupplierID, TheirPartNo and DateofLastOrder and display all those columns as a sub form inside the Product form in descending order. Or you could use the same datastore (table design) and poipulate a combo box.

    there are two separate aspects
    datastore design (the way thge data is stored and therefore retrieved)
    user interface design (the way you display the data you want in the way you want)
    Weejas has provided a query, but it will only work if your data is stored in that manner, just as any other query will only work with the datastore it was designed for.

    but there is no concept of :-
    I need a lookup field "DefaultSupplier" in a table of Products "filtered" to contain only those suppliers WHERE Junction.ProductID=Products.ID
    if you want a default then you need some form column in I suspect the intersection table which indicates if that is the preferred / default supplier. use a boolean value (yes/no)
    the when it comes to retrieving the data include that column. set a sort order which brings that row to the front of the list

    its the same query design that Weejas showed you in post #5 with the exception that you set a specific sort order
    Code:
    ORDER BY mydefaultsupplierflag;
    where mydefaultsupplierflag is the name of your new column.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2012
    Posts
    7
    That was perfectly clear, Thank you very much, this thread is solved

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
  •