Increment for duplicate records. (1 Viewer)

[Nocodexp]

Hi,
I'm pretty new to this forum and I barely have any coding skills. I'm not asking for a direct handmedown as it is for help.
I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.
What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.
What I'm trying to get at is that I would like to get an idea on how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.
An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier
I barely have any coding experience (learned vb 6.0 about 3 years ago and never pracised it, doubt it would be useful anyways), so I won't be able to know everything and anything.
Thanks in advance

 

[plog]

This can be done with just queries, so no VBA necessary. But first, answer a simple question: Why?

What does this do for you? Why not just add an autonumber to the table, let it assign a unique number for everything then use that?

What does this new number allow you to do that the autonumber doesn't? Walk me down the road that this is the first step of.

 

[Nocodexp]

I understand that I could use autonumber, however, it can't track the number of duplicates.
You see, I'm working with parts that have been entered over the past years that have different order numbers, suppliers, and locations, and what is neccessary is that I need to have a format of a unique number as (000000)<-part number (00) <--Duplicate ID (resets for each unique part number). This is what consists of my unique identifier.

 

[jdraw]

I agree with plog that this could be done using queries. But it depends on how well designed your tables and database are.
You do NOT need a composite unique number necessarily. You could use atomic fields. What are the unique fields in your various tables? Can you tell us more about the database or show us a jpg of tables and relationships (zip it)?
Often if you have Parts from different Suppliers, you would have either your own Part numbers mapped to the various Supplier specific Part numbers, or you would use the Supplier Part numbers...
As plog said, Why are you doing this---for what result?
Perhaps if we had some sample data and a clear understanding of why, more focused responses and/or options would be forthcoming.

Good luck.

 

[Nocodexp]

I am doing this as a part of a reform of a unique identification system, so that whenever a new part number is entered in the system, a unique part ID could be created as of a result, instead of having someone create the identification numbers by hand and have someone else enter it in a database of this scale. It is also going to be used to help with providing how many times the part has been entered in the system, just based on its duplicate ID, foregoing the need for a sort and search
The issue is, however, I am not the only one that has modified this database, and I am far from it, it was created with Access 2000, and I'm responsible with bringing it back up to date.
Another problem is that in order to save time, the previous people have done shortcuts, and that means that there are no relationships at all. The table fairly inconsistent, since there are different requirements for different parts, with the only exception being the part number, which is present in every single record.
Its just very hard for me to explain, since the entire database is a mess and I'm trying to fix it, one bit at a time.

 

[jdraw]

If the data base is important to your business, and if the data base has become inconsistent and unreliable, then I suggest you get a mandate from management/users etc to redesign to make it do what you need. It seems from your post that it has been disused/misused and perhaps has fallen out of maintenance (become out dated).

I think you are attempting to adjust a symptom and you need a clear look at what is required to help your business.

Sounds like you might be putting another patch on an obsolete system.

Good luck.