So close yet so far

[Wulf13]

I've posted only a few times, and have gotten some great participation from the wonderful folks in this forum. I'm still relatively new to MS Access, I just took a course through my local college and found this program to have much potential.

Now to the heart of the matter. I need to create a database program to track the following info:

Employee Number/Name/Date Employee number activated/Date Number Suspended

I've got the database started but I need it to do one thing that I can't figure out.

Scenario: Jan 1st 2004 this is what my data might look like

01A/Bob/01 Jan 04
02A/Mike/01 Jan 04
03A/Billy/01 Jan 04

Throughout the year a person might get suspended thus causing a new number to be assigned and there old record to be suspended. Now Jan 1st 2005 rolls around and I need the Employee numbers to remain static but those names that no longer have an "active" number need to have just there name removed from the database and then take the names that are still "active", sort them then assign them a new Employee number

As I stated I'm still new to this program but I was hoping that someone might be able to help me accomplish this task. Thank you

 

[wazz]

you might simply add a checkbox to the table. if someone becomes suspended/inactive, check the box - no need to delete anything, yet. in the new year run a query to find those whose checkbox is true and delete them.
(instead of reassigning numbers all the time, wait until the new year when you do the deletions, then reuse those numbers(?)).

 

[ghudson]

and the Employee number re-assigned to the next person in order.

Your company should reconsider their policy of re-assigning [re-using an existing number] a number.

 

[Wulf13]

Wazz thats what I'm trying to do but I can't figure out how to get Access to fill in the blank after the names have been removed. we only re-assign a suspended number come 1 Jan (I.E. 01A is suspended today, then it doesn't become reactivated until 1 Jan of next year and thats when I need to re-assign it.) Make sense, I hope???

ghudson- actually the employee number is just a reference to a "classified" number and that "classfied" number changes on a yearly basis so reusing a employee number isn't to bad but it only happens at the end of the year when the classified number changes.

 

[Wulf13]

Pat, what do you mean? I need to reuse "index Numbers"every year in my "new" table by taking the remaining names that are still active from last year, sorting them alphabetically and than pairing them up with a new number. You're telling me that is poor practice? How would you suggest I go about this than?

 

[Wulf13]

Example number: 01A, this number is just a number that an individual uses to to reference a certain classified document. If the person leaves, there number is 'suspended'. At the end of the year the classified documents change but reference numbers stay the same. I need to create a function within Access that will go through my table, take out the people that are no longer with the company, and then re-assign the numbers.

We re-assign numbers because we only have a finite selection of numbers to work with. The old hardcopy from the previous year is destroyed and there are no other tables. I only have two tables currently, the first is the employee numbers and the second is employee name, date # issued, date # suspended.

This list is not a senority list, its purely based on alphabetical order.

 

[wazz]

i'm not sure i can actually answer the "how" part, i'm afraid, but let me see if i understand.

Q. if, for example, the people using 01A, 04A, 34B, and 42C have been deleted, you want to keep those numbers in your EmpNum table (this table never changes) and reassign them to new people? i can't help thinking this should be done manually.

Q. it doesn't matter who the numbers are assigned to? it's a random association?

Q. are there people in the table without numbers or does everyone in the table have a number?

Q. in the new year, is everyone deleted (the whole table) and a new table created with null numbers, to which you must add the numbers?

Q. apart from this special number we are talking about, do people have a (another) unique identifier (ID/AutoNumber)?

Q. does the 'special number' have a unique identifier (ID field)?

Q. if we forget for a moment about the routine you have to go through every year, can we just say: you have a null field in your table of people and you want to randomly assign all of the numbers from the number table to the null fields in the people table (one number per field, no repeats)?

 

[Wulf13]

Q. if, for example, the people using 01A, 04A, 34B, and 42C have been deleted, you want to keep those numbers in your EmpNum table (this table never changes) and reassign them to new people? i can't help thinking this should be done manually.

I want to keep those records in the table until the end of the year. Then the number gets re-assigned

Q. it doesn't matter who the numbers are assigned to? it's a random association?

Yes at the beginning of the year when the new table is created we take numbers 01a, 02a, 03a and they get paired with the first three individuals in the list of employees which is alphabetical.

Q. are there people in the table without numbers or does everyone in the table have a number?

Everyone has a number

Q. in the new year, is everyone deleted (the whole table) and a new table created with null numbers, to which you must add the numbers?

Yes and no, I take the numbers and just the numbers from the old table along with those employees that are still with the company. I sort the employee names in alphabetical order and then make a new table using the employee number listing and those employees that are still employed.

Q. apart from this special number we are talking about, do people have a (another) unique identifier (ID/AutoNumber)?

No

Q. does the 'special number' have a unique identifier (ID field)?

No, should it?

Q. if we forget for a moment about the routine you have to go through every year, can we just say: you have a null field in your table of people and you want to randomly assign all of the numbers from the number table to the null fields in the people table (one number per field, no repeats)?

O.k. you lost me here. Maybe this will help, this is a sample of what the table would look like at the beginning of the year.

Employee# Name
01a Bob
02a Billy
03a George
04a (available for new hire)
05a (available for new hire)
06a (available for new hire)

Now sometime throughout the year

Employee# Name
01a Bob
02a Billy
03a George(he's gone)
04a Mike (new hire)
05a (available for new hire)
06a (available for new hire)

Now at the end of the year I need to erase George's name and have Mike take George's old number. I hope this made sense. Manually editing the file would be nice except I'd end up doing it for 1200+ people, not something I'd like to do.

 

[Wulf13]

Pat, just out of curiosity why does it matter as to why we do it that way? I've tried to explain and here it is again. The number we track is just a reference # in a book. In this book is a 2 column list of numbers. The first is the reference # which is what we issue to people. The second number is a number kind of like a PIN that is matched up by reference number. Mind you the PIN is a controlled item and we limit the number of places its recorded hence the use of a reference number. Every year we receive a new book that has the same reference numbers but all the PINs have been changed. You may not understand the process but I can't divulge too much more without getting my self into trouble with my superiors. I hope this has helped you understand the process. Please let me know if it insufficient and I'll see if there is another way to spin it.

As far as what I want my database to do is this:

If Bob has 'Reference #' 01A this year and next year when the 'new' book of PINs goes into effect, and he is no longer with the company, I need whoever is next in order to take Bob's # with the 'new' edition. Am I making any sense here?

 

[Wulf13]

"Because the whole process is sooo archaic and paper-centric that it makes my teeth grate if you really want to know."

Ya mine too, I unfortunately don't have the power or pull to change it though.

Thank you for your help in this matter. I will try what you suggest.