AutoIncrement AlphaNumeric

[Christopher770]

I apologize if this is not the right forum!

1. Table with Two Alpha ID's [TID1] and [TID2]
2. Independently, each are unique
3. I need to concatenate them for form [TID1]&[TID2], no problem
4. but i need to add an auto increment number starting at 1

I created an unbound form with two unbound text boxes and set my row source to look up and populate [TID1] and [TID2]. I used another text box to concatenate. So far easy.

What I need is to do is add an incremental number for the pair in a new Table. This would increment based on the paring of unique concatenated values. I've created the new table to hold the values and using an append query to try and do this, but no success (obviously).

The result of what I'm looking for is like this:

ATLJAX1
ATLJAX2
ATLSAV1
ATLSAV2
ATLSAV3

Now suppose I want to add a new record for ATLJAX, and a new record for ATLSAV#. The result I'm looking for is: ATLJAX3, AND ATLSAV4, to be added to my new table.

I'm not sure the the method I need to take to accomplish this. I'm good with access, but suck at VBA

Any an all help would greatly be appreciated. And, if I need to re-post to another section, I'm sure you'll advise me.

 

[jdraw]

Can you tell us WHY--What I need is to do is add an incremental number for the pair in a new Table..

Please tell us more about your Tables.

 

[Christopher770]

Table1: Location ID = "TID" 5 byte text field. This must be alpha. There are thousands of location id's. To this end, each location ID can be pared with another location id. Consider this as a FROM and TO location, like an airport location. And, they can be paired an unknown number of times, for example a flight from ATL to JAX, Flight 1, Flight 2, etc. But probably not more than 99.

Table2: ODPAIRID = [TID1]&[TID2]&AUTO INCREMENT. Plus other fields that are not relative. This is the primary key for this table I need to create, and use in further processes. This would be indexed and could not be replicated. This is created by user input by. The TID field is a lookup from Table 1. I'm using this to lookup the value I want for each new field to populate [TID1] and [TID2]. Know, I need to know what the last value of the pairing was to create the new Index [ODPAIRD].

So as my previous post has ATLJAX1, the value probably could never be more than ATLJAX99. They must remain ALPHA. Each TID could be 3 to 5 digits long. I have to do further plan for each unique paring, as each represents a movement. But I need to know when a new pairing is created by the user, one that is not planned. So if I plan on ATLJAX1, and ATLJAX2, when the user creates the unknown ATLJAX3, I need bells and whistles to go off.

So, Simply....1 table with a TID field, and other descriptive fields like address, timezone, etc.

I could very easily just use an auto-increment number, however users recognize these values ATL and JAX to have geographical meaning so that cannot be changed. I could probably just create a static table with the max number of parings up to 99 each, but I felt that would increase overhead and degrade efficiency when other processes are looking up these values.

I could just be having a major brain fart, I'll admit. But at the moment am stuck.

Thanks for the reply and help.

 

[RainLover]

Have a look at the attached.

You will have to do some work to get it to work for you.

 

[gemma-the-husky]

Table1: Location ID = "TID" 5 byte text field. This must be alpha. There are thousands of location id's. To this end, each location ID can be pared with another location id. Consider this as a FROM and TO location, like an airport location. And, they can be paired an unknown number of times, for example a flight from ATL to JAX, Flight 1, Flight 2, etc. But probably not more than 99.

Table2: ODPAIRID = [TID1]&[TID2]&AUTO INCREMENT. Plus other fields that are not relative. This is the primary key for this table I need to create, and use in further processes. This would be indexed and could not be replicated. This is created by user input by. The TID field is a lookup from Table 1. I'm using this to lookup the value I want for each new field to populate [TID1] and [TID2]. Know, I need to know what the last value of the pairing was to create the new Index [ODPAIRD].

So as my previous post has ATLJAX1, the value probably could never be more than ATLJAX99. They must remain ALPHA. Each TID could be 3 to 5 digits long. I have to do further plan for each unique paring, as each represents a movement. But I need to know when a new pairing is created by the user, one that is not planned. So if I plan on ATLJAX1, and ATLJAX2, when the user creates the unknown ATLJAX3, I need bells and whistles to go off.

So, Simply....1 table with a TID field, and other descriptive fields like address, timezone, etc.

I could very easily just use an auto-increment number, however users recognize these values ATL and JAX to have geographical meaning so that cannot be changed. I could probably just create a static table with the max number of parings up to 99 each, but I felt that would increase overhead and degrade efficiency when other processes are looking up these values.

I could just be having a major brain fart, I'll admit. But at the moment am stuck.

Thanks for the reply and help.

seriously. users recognizing such values should not influence your thinking. I cannot think that there is ever a good reason to have an incrementing concatenated key. If you need

A001, A002, A003, then store the "A" and the number in two separate fields. Much much easier.

The internal storage model you adopt is not really relevant to the data that users see. That is the point.

for example, given the need to pair ATL and JAX, all you need is junction table including these two fields, indexed. This can have its own single field unique identifier. You say you need multiple instances of these pairs. If so, then add a third column, a numeric identifier.

eg [TID] must be a 5 character identifier. Not at all, although you may have an enterprise rule that mandates one. I would use an autonumber key for all the ID's, and have a second description field, that you can limit to 5 characters or whatever you want. That way, if your model changes in the future, you won't have any issues.

I hope RainLover would agree with all this.

 

[Christopher770]

Rainlover !!! Thank you. This is EXACTLY what I'm looking for.

 

[RainLover]

Hi Dave,

At the moment I don't agree completely with anyone even myself. I say this because I am unsure what the OP wants.

Your suggestion of Two Separate Fields to store AJSZ and AJSX etc and another field for a Long is Simple, Correct and Easy to adjust to suit one's needs.

Dave what you didn't mention (or did you) that you would make these a unique index.

In either case I would still use Auto Number as the Primary Key. The Primary Key will therefore never be wrong. Whereas the combination of two fields could cause a problem. I don't know what sort of problem that could be at the moment. But I do know if there is a future problem it would be difficult to fix.

 

[RainLover]

Rainlover !!! Thank you. This is EXACTLY what I'm looking for.

Chris

There are some difficult things in that sample but the basics are simple.

Sing out if you need more help. Also look at the discussion I am having with Dave about the Primary Key.

This requires some thought on your part.

 

[Christopher770]

Hi Dave,

At the moment I don't agree completely with anyone even myself. I say this because I am unsure what the OP wants.

Your suggestion of Two Separate Fields to store AJSZ and AJSX etc and another field for a Long is Simple, Correct and Easy to adjust to suit one's needs.

Dave what you didn't mention (or did you) that you would make these a unique index.

In either case I would still use Auto Number as the Primary Key. The Primary Key will therefore never be wrong. Whereas the combination of two fields could cause a problem. I don't know what sort of problem that could be at the moment. But I do know if there is a future problem it would be difficult to fix.

Just a quick followup note: I did use the Dmax example provided. I did modify it slightly, because the Two TID fields [ATL] & [JAX] are derived from another table, and these had to be concatenated first, then the new Dmax numeric value determined, and then the final concatenation which provided exactly what I needed. And I did maintain the auto-number as the unique key to the table. Works like a champ!

I do not foresee any down-line issues with this, but I'll cross that bridge if I get to it, which is doubtful in my humble opionion

Thanks again to all!!!

 

[RainLover]

I do not foresee any down-line issues with this, but I'll cross that bridge if I get to it, which is doubtful in my humble opionion

What do they say? Famous last words.

You appear to be traveling in the right direction.

Best wishes for the project.

 

[Christopher770]

lol...I know...I jinx myself when I say that. Thanks again!