Increment Recordset

[JGoad]

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

 

[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.

 

[JGoad]

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