Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    22

    Unanswered: Relate Colour list to unique product id

    Hi.

    I want to select a product then be able to select a colour from a list that relates to it.

    I want the colour lists to be different for every product. (clothes)

    Whats the best way to do it?

    Thanks

    Jonny

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    One option would be to create a table like so:
    ProductColours(ProductID,Colour)

    EDIT:You may wish to have a separate colours table and use the ColourID in the above table as a lookup. It's your call ~ignore this
    Last edited by gvee; 06-13-07 at 11:48.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Posts
    22
    Its related to clothing so each productID has around 10 colours to choose from.

    A lookup would be good, but i need to seperate the values in the lookup list so that the colour list relates directly to the productID

    any thoughts?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Scratch my second comment above - the table structure I suggested should work.
    Eg
    Code:
    Me.MyCombobox.RowSource = "SELECT Colour FROM ProductColours WHERE ProductID = " & Me.txtProductID
    You can easily use it as a look up (is IS a lookup!)
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Posts
    22
    im a beginner, need a little bit of an explanation

    "SELECT Colour FROM ProductColours WHERE ProductID =

    dont see how that will give a list of user defined colours for an individual product?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Colour FROM ProductColours WHERE ProductID = 1
    This will provide us with all the colours that are associated with product 1 in the ProductColours table outlined above.

    Why not give it a go?
    Create the ProductColours table with the two fields; ProductID and Colour.
    Stick a few values into our new table
    Code:
    ProductID Colour
    1 red
    1 blue
    1 pink
    2 blue
    3 red
    3 blue
    4 orange
    Then run our SQL statement

    I hope this is of some help/explanation - ask any questions you need.
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2007
    Posts
    22
    OK, thanks alot, i managed to do that.

    However - sorry for my lack of knowledge...but...

    i have attached the sql lookup statment on the row source of colour in the products table.

    But how do i allow other products to have different selections.

    ie. I can now select from black, blue, red for product 1 but in the long product list i want to be able to select from a different colour list for a different product.

    In my colours table i have product id 2 and the colours are black, blue, pink.

    but im getting the same options for every other product as i am for product 1

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ah so we are wanting groups...
    This changes things a wee bit!

    Assuming one product can only have one colour group then we don't need this table in the middle.
    We need to add a field to our product table; something like "ColourGroupID".

    You then populate this new field with the ColourGroupID from our Colours table.

    Post the structure of your colour table please
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2007
    Posts
    22
    Ahh, im so brain fried by it all!

    here you go - ive removed some stuff for you to see.

    thanks
    Attached Thumbnails Attached Thumbnails relationships2.JPG  

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh dear, we seem to have crossed wires and gotten confused.
    Let's start again.

    Do you want to create colour groups or just set up each colour for each product?
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A quick example using groups...
    Attached Files Attached Files
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2007
    Posts
    22
    Thanks for that, helped alot.

    I take it i can do the same sort of thing with size.

    How can i then specify the quantities of these combinations.

    E.g. If i select a product, then select a colour from its linked group and then a size from its linked group can i then enter in a stockquantity for this particular combination?

    thanks again

    Heres an updated screenshot to.
    Attached Thumbnails Attached Thumbnails relationships3.JPG  
    Last edited by j0nnyd; 06-18-07 at 12:01.

Posting Permissions

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