Increment Recordset

G

Guest

I want to write code that increments items in a recordset by 1.

Here are the criteria:

ItemID (ITEMS table) is a derivative of its owning employee's ID + a
1-letter suffix (increments by 1, providing up to 26 items per employee)

EmployeeID (EMPLOYEES table) is a derivative of the department's ID + a "-"
and a 2-digit suffix (increments by 1). If there are more than 99 employees,
the next value would be A0, A1, A2… A8, A9, B0, B1, B2… (Providing 359
possible employees per department)

DepartmentID (DEPARTMENT table) is always associated with a company ID and
is a "-" and a 4-digit suffix (not necessarily unique)

CompanyID (COMPANY table) is a 4-digit number (unique).

Therefore:

One could look at an item's ID and derive from that the employee to whom it
belongs (as well as the company and department):

1111-2222-01A

One could look at an employee's ID and derive from that the company and
department to which they belong:

1111-2222-01

One could look at a department ID and derive the company:

1111-2222

How do I increment the EmployeeID automatically by 1 (following the criteria
above… always 2 digits with possible alpha)
How do I increment the ItemID automatically by 1 (following the criteria
above… always alpha)

Regards,

John
 
B

BruceM

I thought I had posted a reply to your question. Actually, it included some
questions of my own. Is there some reason you don't just use three digits
for the employee ID? More to the point, do employees ever transfer to
another department, and do items ever get transferred to another employee?
As it is the item's identity depends on whoever owns it at the moment, so
presumably the ID would change if an employee leaves and the item remains in
service. Why not just give the item a unique ID of its own? Also, are you
sure there will *never* be a day when an employee will own more than 26
items? It is possible to increment numbers automatically, but it's hard to
know where to begin in your situation, since every number depends on another
number, except for CompanyID.
In general you would assign the numbers, then combine (concatenate) them as
needed. If you need a list of items an employee owns, or any other such
list, you can do that without having to interpret a number. If you prefer
to interpret a number (and look up employees on a separate list) you can
still do that if you concatenate the numbers.
This is the situation as I can understand it so far: There can be up to
9999 companies, each of which can have any number of departments (since
these values do not need to be unique there is no limit on the number), each
of which can have up to 359 employees, each of whom can own up to 26 items.
It would help if I knew something about the real-world situation behind this
project (for instance, what are all these companies, and what is an item),
but I can tell you that as described the numbering system is at best highly
complex, and in any case would probably be an administrative nightmare.
 
G

Guest

Do employees ever transfer to another department

- No, very specific

Do items ever get transferred to another employee?

- No, they exist because the empolyee provides/produces them. Once the
department is dissolved, the employee is gone, so is the item.

So far, we have more than 900 different "employees" and about 2500 items
accounted for using this nomenclature that I don't want to just "trash" and
rename, especially since they are still referred to but not necessarily used
or even exist -- the items, not the people -- even after the department is
dissolved.

Are you sure there will *never* be a day when an employee will own more than
26
items?

- No, I'm not sure, but, to date each employee has only been *required* to
have 1 item, usually actually has 2, and, so far, none has any more than 8.
Because of the nature of the items, it is not likely to be more than that,
but I wanted to provide for more; alpha satisfies more than 3 times that and
takes up only one placeholder.

I am not concerned about incrementing departments (which is, by the way, an
8-digit number, which includes the companyID, not a stand-alone 4-digit
number) automatically; they are actually assigned per company. What I meant
about not being unique is: Company 1111 can have department 1111-1111 and
Company 2222 can have department 2222-1111. BUT the tail 1111 could mean
"1313 MockingBird Lane Investigation" for Company 1111 and "County Zoo" for
Company 2222. There is no standard set across companies and, because of
their nature, there can be literally thousands of departments for 1 company
and only a handful for another.

This is more of an "Our-Client/Their Client" thing. We hire companies who
perform specific jobs (substitute "departments") and provide people who, in
turn, provide/produce items. Employees don't "cross over" to other
departments. Once a job is finished, the department is marked finished and
all items either destroyed or shipped to the department project manager.

But for each employee, I want a way to automatically increment and for each
item I want a way to automatically increment.

Thanks again,

John
 
B

BruceM

Certainly these are not employees in any usual sense of the word. It sounds
as if they are one-time only, gone once the department is dissolved, never
to return. If they do return in some other capacity I would think you would
want a consistent way of identifying them. The typical way of identifying
employees is with a unique number or alphanumeric code. I assumed a
conventional sense of the word "employee".
I did a quick group search for: "microsoft public access" increment
alphanumeric. Here is the link to one of the search results:

http://groups.google.com/group/micr...ncrement+alphanumeric&rnum=3#ff9cb16c21904051

Your system for employee numbers is quite complex, and will require no small
amount of coding. A lookup table may be the best solution. To make matters
even more complex, since the employee ID is a combination of company +
department + ID you have lots of employees with the ID 1.

Incremented number problems are of interest to me because of some projects
of my own, so I watched this thread. When there was no reply after two days
I ventured some thoughts and suggestions, but I do not have a clear idea of
how to go about combining so many variables. The only solutions I can think
of involve storing data redundantly. That is, the stored EmployeeID would
actually be the previously-stored CompanyID and DepartmentID fields with a
unique number added to the end. The item would be added to the end of that
for yet another redundant number. I don't want to suggest storing data
redundantly, as that can lead to its own set of problems.

Having said that, here is some code I devised in the past with some help
from this group. I have adapted it from the two-digit year (which I used)
to CompanyID, but I have not tested it. Storing CompanyID as part of the
number may not be ideal according to some, but if I store only the
incremented part of the number then that number cannot be unique, so I would
need to use a multi-field primary key or a separate autonumber field or
something as the primary key. If I do that I'm not convinced I'm gaining
much over storing CompanyID. Many will disagree. Maybe my suggesting it
will bring then into the thread. I will say that perhaps a string can be
declared, then substituted for the field YourNumber in the code. YourNumber
could be the rightmost four digits from the code. You could use a separate
autonumber field as the PK. Again, I am speculating a bit since I don't
have time to test this, but something like that could help get you on track.

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "YourNumber Like """ & CompanyID & "*"""
varResult = DMax("YourNumber", "tblYourTable", strWhere)

If IsNull(varResult) Then
Me.YourNumber = CompanyID & "-0001"
Else
Me.YourNumber = Left(varResult, 5) & _
Format(Val(Right(varResult, 4)) + 1, "0000")
End If
End If

A groups search for "incremented number" or "increment by 1" or something of
the sort will lead you to a variety of discussions about this topic,
although I have to say that yours is about the most complex situation of its
sort I have encountered in my research on this subject, especially
considering that some of your incrementing is by number, some by letter, and
some by a combination of both. If you include

Sorry I couldn't be of more help. The only other thing I can suggest other
than that you try some group searches is that you devise a way of
incrementing employee IDs by a method such as described in the link. Forget
about company and department, and just get EmployeeID working. Once it is
you can look at ways of combining it with other numbers.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top