Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2011
    Posts
    9

    Unanswered: Can I automatically generate a SKU in Access?

    I was wondering if I could enter the values in Access it would be able to automatically generate a SKU for all my products? Is this possible?

    Cheers.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes it possible
    providing you can codify how an SKU shoudl be created

    in an ideal world all you goods would come from external suppliers with somehtign like an EAN allocated already. you then have to allocate your own numbers in a way that doenst' conflict with numbers allcoated elsewhere
    http://en.wikipedia.org/wiki/Interna...le_Number_(EAN)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Posts
    9
    Quote Originally Posted by healdem View Post
    yes it possible
    providing you can codify how an SKU shoudl be created

    in an ideal world all you goods would come from external suppliers with somehtign like an EAN allocated already. you then have to allocate your own numbers in a way that doenst' conflict with numbers allcoated elsewhere
    http://en.wikipedia.org/wiki/Interna...le_Number_(EAN)
    Thanks for the reply.

    The invoices do not appear to have an EAN but they do have an "article" number using numbers and sometimes letters varying from supplier to supplier. I didn't think about including the suppliers' "article number". I thought I would just make my own with the colour, size, etc.

    How do I do this in Access, anyway?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have products coming in with a stock number + barcode then I'd strongly suggest you use that. most serious suppliers will have a unique item number per product. one thing to be cautious of however is that sometimes the supplier uses thesame barcode / article number on a case / product multiple as the individual item.

    as to how you use it in Access is up to you
    you could use it as a primary key or you could use an autonumber primary key. as to which choice you sue its up to you. if you know that you will never ever have a dupicate product then using the provided number as your PK makes sense

    if you know the product number at the time of data capture then its reasonable enough to use it as a PK. hoever if you store products prior to knowing the article number then that could cause problems if you use it as a primary key


    if there are susbtitute products witht he same article number (there shouldn't be the EAN/IPN schema uses 13 digits and is supposed to uniquely identify a specific product from a specifc manufacturer. however not all suppliers are signed up to the EAN/IPC scheme.

    storing the supplier ID is alwasy a smart call.. its how they identify their products so they will be more familar with their own item id as opposed to your item ID. there is also a risk that descriptions may cover similar items due to typos or other issues.

    as said before the problem is how you define your product codes. As EAN/IPC is a global standard that woudl be 'the' schema I'd use. there should be some information on how to setup internal codes

    how far have you got with your design
    how do you plan to cover the situation where you have the same design product but different sizes and or colours. you cna argue both ways, each item is a separate product OR you have a top level prodcut, then a colour way associtation table, then a size association table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Posts
    9
    Wow, I appreciate the detailed reply.
    Duplicate product? I looked on the invoices and the article number is for the product but there are multiples of them (bought in fifties and hundreds). There are also multiple suppliers.

    I'm not familiar with IPC scheme. The suppliers tend to have codes like 4321 or 1234YX (on the invoice). It doesn't seem like EAN. I'm not sure if the EAN/IPC codes were on the packaging since I never dealt with that and they have most likely be thrown away.

    I haven't got really far because I seem coming up with problems. I thought I might ask in another thread after I have finished this issue.
    So, your recommendation would be to simply use the manufacturer's article/SKU? I will at the very least store it now. They are often very different as I explained above, do you think this will create problems? Or have I not understood properly?

    My original thought was the latter. I was going to associate the various colours (sometimes the product has multiple colours) with the product ID (or the supplier’s product ID). Same with sizes. Do you think this would work?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look on the products themselves for an EAN/IPC/JAN code. if the product is barcoded then almost certainly its an EAN/IPC/JAN code.
    if the products are supplied with no barcode then its less likely to be covered by the scheme.

    in an iodeal world you should use a prexisting numbering system, but if you cannot rely on the suppliers then you roll your own. granted Im asssuming you are modelling a real world retail system, if its a non retail system then its highly likely that EAN is not used.

    there are blocks within EAN which are reserved for internal use see
    Retail Numbering Systems for Products (PLU/SKU), Customers, Clerks and Coupons

    To progress further I think you need to give a bit more detail.

    part of the design depedns n the complexity of the system you are trying to model who is going ot use the system and how.

    one of the biggest stumbling blocks in retai systems is the switch to electronic tills with no pricing means that the data capture and mainentnace is critical.

    fer instance you need to make certain that every product stocked has a valid product number (and that product number and pricing) is known to the till. far too often products leave the storeroom for the floor without the price being correct (witnedd Tesco's problems with beer promotions) and / or without a correct product number. the latter is easily enough solved if the product is scanned before it leaves the stockroom and/or before it enters the stockroom.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    In my previous role where I currently work, I used an Access application to issue new SKUs and barcodes for new products.

    Do not store meaningful information within the SKU itself - it leads to problems down the line. Ideally, they should simply be a form of serial number. A decision was taken by someone in the dim and distant past here to use different structures for different parts of the business, so that you can tell at a glance if an SKU is lot controlled or not, and which part of the business owns it, but that has caused difficulties. Any information about the SKUs should be stored in the other fields, so that you can have flagging codes that indicate colour, size, shape and anything else that needs to be reported.

    I would also second Healdem's suggestion to include the supplier's part number, but I would modify it to recommend that you have a separate table for this. That way you can record numbers from multiple suppliers for the same SKU.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jun 2011
    Posts
    9
    Thanks to you both for the responses.

    We are retailing but the suppliers are wholesale/manufacturers. They are buttons, ribben, etc. It sounds like they should have EAN but they are obviously not printed on the buttons themselves. I am not sure what detail to give honestly.

    I want to keep extra data index locally and then a reduced version shown online.

    <URL removed by gvee on request of the author>

    It is probably a complete mess.

    Again, I really appreciate the help.
    Last edited by gvee; 07-24-11 at 16:58.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Remove the direct line from vendors to products. That's what suppartno is for. Generalsize and Color should work in the same way as type.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jun 2011
    Posts
    9
    Quote Originally Posted by weejas View Post
    Remove the direct line from vendors to products. That's what suppartno is for. Generalsize and Color should work in the same way as type.
    I have got rid of the relationship. Should generalsize have a productID as the PK or generalsize?

    Cheers.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your SKUs should reference the PK of the subsidiary information tables, not the other way around. generalsize and color should have their own PKs, and these key values are what should be quoted in products.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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