Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    99

    Unanswered: how to generate product codes

    Can anyone help me out here please?

    I have a database of 800 products with codes & descriptions but this list is going to have to grow quite rapidly to also include a colour and size code.

    At present it looks like this:-

    G02-02 T-Shirt

    but I want the code to change to be like

    G02-02/SRE T-Shirt Small Red (added the colour and size variant)

    Each product may have at least 5 colours and 5 sizes so across 800 products this would be mind numbingly dull!!

    Is there any way to automatically generate the new codes and also to expand the description to then include the added info e.g. Small and Red.

    Many thanks.
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    There is always a way! ( usually )

    How is your product data stored right now? Is the information stored nicely in database tables and in seperate fields?

    Is this code value that you want to change what you have stored or how you are viewing it. What is the code? It does not look like a code in the sense of a product id so I am confused on what you are trying to do.

    Provide a bit more detail?

    Steve

  3. #3
    Join Date
    Feb 2004
    Posts
    99
    Sorry I did not provide enough detail....

    all data stored in one table...

    productid = e.g. G02-020
    productdescription = e.g. T-Shirt

    I then want to generate the permutations of these codes and descriptions so that it can create a new code and description

    productid = G02-020/WHS
    product description = T-Shirt - White Small

    the first character in the product denotes category (e.g. G = clothing where 5 colours are available and 5 sizes are available, A = Gloves where no colours but maybe 6 sizes etc)

    Can it be worked that it identifies the product category (by the first digit and then adds the various permutations in colour and size and adds the extension to the productid and the product extension?)

    many thanks
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  4. #4
    Join Date
    Feb 2004
    Posts
    99

    how to generate product code variants/permutations?

    Hi, this is somewhat of a repost as I've tried to clarify things as much as posible.

    I'm using MS Access and have a table with products.

    The table has for example:-

    productid = G02-020
    production description = T-Shirt

    The first digit (G) denotes clothing and there are 5 colour variants and 5 size variants...

    I want to generate new productid codes and product descriptions for the 25 permutations of the root productid G02-020 so that....

    G02-020 becomes G02-020/RES
    T-Shirt becomes T-Shirt Red Small
    G02-020 become G02-020/REM
    T-Shirt becomes T-Shirt Red Medium
    G02-020 become G02-020/REL
    T-Shirt becomes T-Shirt Red Large etc ad nausea....

    This is for about 800 products but the logic would be that the first character would (largely) indicate what colours and sizes are permissible e.g. Gloves A where colours are not an issue but there may be 5 sizes.

    many thanks, i know the truth is out there somewhere just not sure how to get to it!
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a CROSS JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pachamama
    all data stored in one table...
    All data? Every piece of information about products, customers, clients, payments etc in one table?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Posts
    99
    No not all data just the data for the products

    productid
    productdescription
    taxable
    status
    cost
    price
    stock

    the first two are the only ones with "live" data, the last 5 fields are filled with spurious info for the moment

    Thanks for any help
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a table with 5 rows for colours, create another table with 5 rows for sizes, and do a CROSS JOIN of these with your products table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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