Hi Guys,

As per the example below, I have two columns (A & B) which I have that are to be joined along with a sequential number to make up part of a final number (column C). Columns A & B need to be joined and then an additional number must be appended to create a unique number. The final sequential number though must be unique per the value in column A. Columns C shows a manual example of what the result should be only having columns A & B to work with. Do you have any ideas how this could be done with Excel formulas or SQL/Access code?:confused::rolleyes:

Column_A Column_B Column_C
103 AAA 103-AAA-001
103 AAA 103-AAA-002
103 BBB 103-BBB-003
103 BBB 103-BBB-004
103 BBB 103-BBB-005
107 AAA 107-AAA-001
107 AAA 107-AAA-002
107 KKK 107-KKK-003
107 BBB 107-BBB-004
107 BBB 107-BBB-005

Are you trying to do this in Access or Excel? In Access you would do it differently.

First you would add an autonumber field to your table. This would be your primary key. Next you would add a Integer field (call it Increment).

When you add a record to your table you would populate the Increment field with an expression like the following:

Me.txtIncrement = Nz(DMax("[Increment]","tablename","[fieldA] = " & Me.txtfieldA & " AND [fieldB] = '" & Me.txtfieldB & "'"),0)+1

Finally, when you want to display the full code you would use an expression like:

=[fieldA] & "-" & [field]B & "-" & Format([Increment]."000")

Quote:

---

Originally Posted by ScottGem

Are you trying to do this in Access or Excel? In Access you would do it differently.

First you would add an autonumber field to your table. This would be your primary key. Next you would add a Integer field (call it Increment).

When you add a record to your table you would populate the Increment field with an expression like the following:

Me.txtIncrement = Nz(DMax("[Increment]","tablename","[fieldA] = " & Me.txtfieldA & " AND [fieldB] = '" & Me.txtfieldB & "'"),0)+1

Finally, when you want to display the full code you would use an expression like:

=[fieldA] & "-" & [field]B & "-" & Format([Increment]."000")

---

Thanks Man!:)

Awesome!!

:):):)