Grab first three letters of word & Index until different

[Rude Awakening]

Hey All
Its easy to do this in Excel but Access apart from the general stuff to me is difficult.

Basically I need to create Unique ID's for requirements. Each requirement has a program and the requirements are based on the program.

There is a field(record) in the table for Program, so What I need is for Access to grab the first three letters of the program name, add a three character integer after the grabbed letters, and increment the number each time the three letters are the same and then to re-indent from 000 when the letters change i.e.
Google(Goo), Yahoo(Yah), MSN(MSN)

I don't mind if I need to have a column with the three characters pulled, but would prefer not to.

Cheers folks

Google	GOO	GOO - 001
Google	GOO	GOO - 002
MSN	MSN	MSN - 001
Yahoo	YAH	YAH - 001
Google	GOO	GOO - 003
MSN	MSN	MSN - 002
MSN	MSN	MSN - 003
Yahoo	YAH	YAH - 002
Yahoo	YAH	YAH - 003
Yahoo	YAH	YAH - 004
Google	GOO	GOO - 004
MSN	MSN	MSN - 004
MSN	MSN	MSN - 005
Google	GOO	GOO - 005
MSN	MSN	MSN - 006
MSN	MSN	MSN - 007
Yahoo	YAH	YAH - 005
Google	GOO	GOO - 006
Yahoo	YAH	YAH - 006
Yahoo	YAH	YAH - 007

<tbody>
</tbody>

 

[Joe4]

What is important to understand about Access is it is very different than Excel. In an Access table, the records are all independent of one another and order really has no meaning (people often describe an Access table as a "bag of marbles"). So tasks which are easy for Excel and not necessarily easy for Access, since they are different programs designed to do different things.

If you just have a table of records that you need to assign the IDs to (as a "one-time" thing), the easiest thing to do is to export to Excel, create your IDs, and import back into Access.

If you want to create a process where you want to do it in Access because you will be adding new records in and need the IDs assigned at that time, you will need to create VBA code to do that.

For already existing entries, you would need to create a query that sorts that records first, then use DAO or ADO recordsets in VBA to loop through them and update them. You would use this method if you are importing records.

For new records added manually, you would create VBA code attached to the an Access Form that populates the field whenever a new record is added, if done manually (no one should ever manually enter Access data directly to Tables, all data entry should be controlled through Forms).

 

[Micron]

I would approach this a different way. Not necessarily a better way, just different. In my opinion (and I have read posts where others concur) such a calculated value of mixed data types (text and numbers) are best kept apart. When using a form, one control contains the text part, another a number. These can be hidden if you wish to have a third textbox that concatenates the string so that it appears seamless instead of trying to juxtapose and right/left justify values. When creating a new record, the process can grab the Max(myNumberField) WHERE NamePart = "Something" and add 1 and save the record with that number. Another useful design element would be to make the two fields a combined index so that
Goo-001
MSN-001
is allowed since together, they are unique. This would prevent anyone from editing a number in a way that causes duplication. One consideration is, how important is it that the numbers are sequential with no gaps? If not, then the new number can be created at the start of the process and if cancelled, the number is simply discarded. If important, I'd create the value at the end of the record creation process as long as it doesn't create a child-without-parent situation. If you must have no gaps, you can only allow editing of every record field but the text and number part of this calculated value.