Please check given below required input table and required output table. I am looking for solution which must generate unique record number for each record but whenver Tproduct change or new one come then Titem number must reset and start from 1.
In fact, I believe sujjeth gave with some screenshots. I am not a good tutor to explain. Some how?
Tproduct & '-' & Left(Titem,1) & AutoNumber(RecNo(),Tproduct) as [Unique ID]
Let's bifurcate to each what actually doing here is
Tproduct & '-' & Left(Titem,1) returns
Tproduct Titem Output
P1 T1 P1-T
For "Tproduct" unique rows it will generate the sequence numbers till row got changed from 1 to total sequence rows of each unique. That is how "Autogenerate" works for "AutoNumber". Instead of asking, Why can't you think by yourself to understand the sequence
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)