473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Creating a unique id in Microsoft Access

I am creating a table with a number of products under different categories. For example,
Product - Men's running spikes which would come under category athletics, and Product - Football boots which would come under category football.

I want each category products to have its unique id which relates to the product.
Example - ID ATH1 (ath - for athletics) men's running spikes and FOOTB1 (footb - football) football boots.

How do I get that done?

Hope I am clear
Oct 27 '11 #1
2 4989
ADezii
8,834 Expert 8TB
  1. You must know ahead of time the specific Category to which a Product belongs.
  2. You must have a Unique 3-Character Code for that Category followed by a Numeric Value:
    Expand|Select|Wrap|Line Numbers
    1. Athletics         ATH0022
    2. Home & Garden     HAG0008
    3. Auto              AUT0122
    4. Appliances        APP1234
    5.  
  3. You must now find the last entry in the Table (tblProducts) for that Category (ATH), then increment the Numeric Component by 1.
  4. Assuming a new Product in the Athletics Category needs to be assigned a Unique ID. Based on the last Athletic ID (ATH0022):
    Expand|Select|Wrap|Line Numbers
    1. Public Function fReturnUniqueID(strCategory As String) As String
    2. Dim strLastProdForCat As String
    3.  
    4. strLastProdForCat = DLast("ID", "tblProducts", "Left([ID],3) = '" & strCategory & "'")
    5.  
    6. fReturnUniqueID = Left$(strLastProdForCat, 3) & _
    7.                   Format$(Val(Mid$(strLastProdForCat, 4)) + 1, "0000")
    8. End Function
    9.  
  5. Function Call:
    Expand|Select|Wrap|Line Numbers
    1. fReturnUniqueID("ATH")
  6. Function returns:
    Expand|Select|Wrap|Line Numbers
    1. ATH0023
Oct 27 '11 #2
NeoPa
32,556 Expert Mod 16PB
I would add that it's probably a good idea to maintain all your categories in a separate table which would contain both the code and the full name of the category. Linking on partial strings is never a good idea so I'd also use an AutoNumber value as the PK and store an FK reference to this table in each product record too (Or you could use the three character string as a PK but store this separately in the product record as an FK).
Oct 27 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Victoria Holowchak | last post by:
I was hoping that Microsoft Access 2002 would allow users to see all the columns of an Oracle database table that contained more than 255 columns. I noticed that my Oracle8 ODBC driver is only at...
7
by: MLH | last post by:
Building Applications with Microsoft Access 97 is a publication I think I need. Is it available in book form? Is MicroSoft the sole vendor? Anybody got a copy they wanna sell???
6
by: Helmut | last post by:
Hello, I am Helmut, my database has a Frontend of 15.4 MB which cause Acc2K to corruption: Limit is 12.5 MB. Service Pack 3. On three different PCs with 256, 512 and 1.000 MB RAM. All on W2K,...
6
by: Null Reference | last post by:
Anybody here who can explain or point me to a link ? I wish to create a blank MS Access DB file programmatically using C# . Thanks, nfs
17
by: Pam Ammond | last post by:
I need to use Microsoft Access Automation within a Visual Studio 2003 program written in C# for Windows Forms. When a button is clicked in my VS.NET program, I want it to run a Microsoft Access...
2
by: ruca | last post by:
Hi, I'm currently facing problems in my Web Application when I try to use a Microsoft Database. With SQL and OrACLE works fine. I only can access with a user at time. No more than one user....
1
by: somersbar | last post by:
hey, ive been trying to set up a web form in visual basic.net that can access a microsoft access database. i need to use odbc also. i can get it working using a windows form but not a web form....
5
by: Greg Corradini | last post by:
Hello All, I'm attempting to create multiple dictionaries at once, each with unique variable names. The number of dictionaries i need to create depends on the length of a list, which was returned...
11
by: breal | last post by:
I have three lists... for instance a = ; b = ; c = ; I want to take those and end up with all of the combinations they create like the following lists
24
by: joeldault | last post by:
Question For Microsoft Access Data Base -------------------------------------------------------------------------------- I am Trying to create a single formula that would do the following: If...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.