Results 1 to 10 of 10
  1. #1
    Gregsfoot is offline Novice
    Windows 11 Office 365
    Join Date
    Apr 2023
    Posts
    4

    Autogenerate unique SKU number based on fields selected from various tables

    I am trying to design a database to automatically generate new unique SKU numbers based on the following parameters.


    I want to be able to select the gender from a table, automatically insert the current year, select a user ID from a table, auto generate the style number and and colour.
    The problem I have is that the generated SKU must be unique, no duplicates, but the style number can have variations, example B230001A (example Blue), B230001B (example red), PB230001A (blue), PB230001C (example yellow)

    Example SKU: B230001A

    B = the gender

    • B= Boys
    • G= Girls
    • TG= teen girls
    • TB= Teen boys
    • PB= pre boys
    • PG = pre girls
    • M= Mens
    • L = ladies
    • TD = Toddlers

    USER ID

    • 0 = GREG
    • 1= Keri
    • 2 = Shalin etc

    Colour

    • 1st colour is A, Second B, third colour C etc

    Attachment 50088

    I have created separate tables for Gender, Users, Year, Style No. and colour, but How do I bring these all together to generate the final SKU number?
    Attached Thumbnails Attached Thumbnails example style number.jpg  

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I don't think you need to store this, just display it by concatenating those values from the core data.
    I assume you have a data table where these values are stored from the lookup tables you have created.

    The purpose of this is a little obscure, what is the overall end requirement?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    UserID should not have any influence on a SKU I would have thought?

    I would probably just create a new SKU as a new product is taken onboard.
    Use Dlookup() or DCount() to ensure final value is unique (by concatenating your indiviual segments
    Use Dmax() to get the latest numeric used and increment by 1. This is a common question asked here on other sites, so look at Similar Threads at the bottom of this thread.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Gregsfoot View Post
    I am trying to design a database to automatically generate new unique SKU numbers based on the following parameters.
    I want to be able to select the gender from a table, automatically insert the current year, select a user ID from a table, auto generate the style number and and colour.
    The problem I have is that the generated SKU must be unique, no duplicates, but the style number can have variations, example B230001A (example Blue), B230001B (example red), PB230001A (blue), PB230001C (example yellow)

    Example SKU: B230001A

    B = the gender

    • B= Boys
    • G= Girls
    • TG= teen girls
    • TB= Teen boys
    • PB= pre boys
    • PG = pre girls
    • M= Mens
    • L = ladies
    • TD = Toddlers

    USER ID

    • 0 = GREG
    • 1= Keri
    • 2 = Shalin etc

    Colour

    • 1st colour is A, Second B, third colour C etc

    Attachment 50088

    I have created separate tables for Gender, Users, Year, Style No. and colour, but How do I bring these all together to generate the final SKU number?
    I can not access your attachment. You might want to try again.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Gregsfoot is offline Novice
    Windows 11 Office 365
    Join Date
    Apr 2023
    Posts
    4
    @Minty - I guess you are correct, I dont need to store these values, I can use concatenation for this. Its application is in footwear design. Each design CAD needs to be allocated a unique SKU which is then linked to an existing order tracking database once the product is purchased.

  6. #6
    Gregsfoot is offline Novice
    Windows 11 Office 365
    Join Date
    Apr 2023
    Posts
    4
    The UserID is included so that we can identify which designer created this specific product.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Gregsfoot View Post
    The UserID is included so that we can identify which designer created this specific product.
    Ok, that make sense. However do you want multiple 23000 for each gender/user/colour?, or would each increment by 1 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Gregsfoot is offline Novice
    Windows 11 Office 365
    Join Date
    Apr 2023
    Posts
    4
    Quote Originally Posted by Welshgasman View Post
    Ok, that make sense. However do you want multiple 23000 for each gender/user/colour?, or would each increment by 1 ?
    Each element of the SKU number will have multiple options for example B230001A (example Blue), B230001B (example red), PB230001A (blue), PB230001C (example yellow)
    The Style Number part of the SKU will increment by 1, but only if I choose to add a new style number. The final generated (or concatenated) SKU needs to be a unique (no duplicate number)
    I have created a simple database as suggested above using concatenation, but the issue with this is that one can easily create duplicate SKU numbers, which I dont want.
    Im not sure how to ensure that the concatenated SKU number isn't duplicated.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    One way is to make a compound index of those fields and make it unique.
    A DLookUp()/DCount() could also work in a function.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    a unique SKU which is then linked to an existing order tracking database once the product is purchased
    If the value is used to link it to another table, save it in a separate field. Never link on calculated fields.
    Last edited by NoellaG; 04-11-2023 at 02:33 AM. Reason: typo

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

Similar Threads

  1. Replies: 43
    Last Post: 01-20-2018, 07:27 PM
  2. Autogenerate Number on a form.
    By hursan1710 in forum Forms
    Replies: 3
    Last Post: 11-10-2016, 09:23 AM
  3. Replies: 1
    Last Post: 08-17-2016, 03:04 PM
  4. Replies: 24
    Last Post: 09-01-2010, 02:09 PM
  5. How to autogenerate a project number?
    By Trinity99 in forum Programming
    Replies: 1
    Last Post: 09-20-2009, 02:11 AM

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