How to assign certain data in a pattern?

[Jonathan Austin]

My question is basically how would I be able to assign certain data automatically in a pattern (i.e. If employee1 is Alpha, employee2 is Bravo, employee3 is Charlie so on and so forth). How would I go about this?

[munkee]

In what context would you like to be assigning the data?

Do you mean everytime you add an employee in to a table they get assigned a unique "Code name"?

[Jonathan Austin]

No I mean after the employees are already inputed, I sort them by Last Name Alphabetically, and then by Gender (Male and Female of course) Then all I have to do is click a button to assign a company, by going down the list... Alpha, Bravo, Charlie, Delta, Echo etc...

[Jonathan Austin]

Rather VBA will assign them a company after I click a button.

[NeoPa]

Jonathan, You need to be more specific in your responses and your question originally.

Do you mean that there will be a field in your table to hold the assignment of the record to a Greek letter from Alpha to Omega and that each time you click the button the next value in the sequence is assigned to the current record?

Would the sequence start at Alpha every time the form is opened?

Without this information we are only guessing what you mean. We may generally be good at that, but it's not clever to rely on it. It does waste so much of everybody's time after all.

[Jonathan Austin]

Pardon my not so descriptive question. This is whats happening....

I have a database of my soldiers. As I enter a 'New Soldier' into the system, I want Access to assign certain data automatically in a sequence pattern. In this company assignments, A, B, C so on and so forth. (We are not using Greek letters. Alpha, Bravo, Charlie etc... are military letters.)

Example... If SoldierA was assigned Alpha Then
SoldierB is assigned to Bravo
so on and so forth.

Hope this is more explanatory then before.

[NeoPa]

Jonathan:
Hope this is more explanatory then before.

Only in as much as I now recognise (as I should have before to be fair) that you are using phonetic alphabet rather than the Greek one. Charlie was the giveaway, but I missed it.

1. Otherwise, how are we supposed to know what you mean by SoldierA?
2. When is the sequence reset?
3. What is expected to happen when the sequence attempts to go past Zulu?

Unfortunately, instead of responding to my post with the questions in it directly, you have attempted to describe the situation again from scratch. A successful attempt would have allowed us to proceed regardless, but this leaves us right back at first base I'm afraid. If you respond directly we can certainly make progress.

[Jonathan Austin]

What I want Access to do is using only a set letters (A through E), going through each record in squence, then resets back to A. So for every new soldier (or record), the soldier will be assigned to one of the companies A, B, C, D, or E (based off of the previous record.) So in otherwords if the previous soldier was assigned A, then the next will be assigned B, then C all the way to E. Once E has been assigned then the squence resets back to A. I hope this clearifies a little better.

[Jonathan Austin]

Its like AutoNumber except with letters, and it resets after the fifth letter, (Letter E in this case). I think this explains alot, considering I just figured it out.

[NeoPa]

I've numbered the questions in post #7 so we can refer to them unambiguously.

It seems that Qs 2 & 3 are fully covered by your post #8. Where we still have confusion is in Q1. This is probably due to a quite common misunderstanding about databases where people tend to assume that the data is stored in a way similar to how it is seen in a grid, or in an Excel worksheet.

If you think of the data being stored instead in a bucket, where all the data is jumbled up, then you have a clearer picture of how it works. The process of getting the data back out is where any ordering or sequencing is applied. I could ask for that data ordered by one field, then SoldierA would refer to one record, but I could also ask for it ordered by another, then SoldierA would refer to a completely different record.

When I ask then, what determines how we recognise SoldierA, I need not only to know that it is the first in the sequence, but also what that sequence is. This is even more important when we get on to trying to determine what the previous record was at any point, as opening the database on any day will need to determine that information before it is in any position to assign a new value to the next record entered.

I'm assuming for now, that you are looking to maintain a dataset such that any new record added via a form can have the value of this field determined as it is saved away. Please let me know if that is a correct assumption, or whether you are intending a retrospective bulk update (which would require SQL work and a whole different approach).