Access???

[Wulf13]

I need to make a database that Tracks employee numbers, who has them and if the number has become suspended. Would this be something that I be better off using a single table or multiple tables?

 

[sfreeman@co.mer]

If you just need to track a few fields of data, and all fields are in a one-to-one relatiosnhip, then one table would likely do it.

If, however, you have one employee with many employee numbers, then setting a a proper table relatiosnhip would be more appropriate.

HTH

 

[Wulf13]

Yes one employee could have multiple numbers but only after his previous number was suspended. I'm thinking maybe a table with just employee numbers and another with employee #'s, names, sections. Does that sound right?

 

[sfreeman@co.mer]

Well, without knowing more, yes, that sounds right. Unless, of course, you need to maintain any sort of history or record of transactions.

 

[Wulf13]

I do need to keep a history and once a year I need to be able to do a mass update. Basically if Bob has employee #1 but it gets suspended and now he's gone, at the end of the year we reassign employee #1 to Mike who was employee #2 the previous year. Does that make sense?

 

[sfreeman@co.mer]

Yep. But I don't think that scenario requires a history or transaction table. Just reassign the number if you don't care who had it previously.

 

[Wulf13]

Would there be away to program the database to re-assign the number say when a button is clicked. Manally re-assigning 1200 numbers to 1200 employees is a pain.

 

[Robert88]

Use a query to apply constant change to all Employee Numbers.....

Can this number be constantly applied to your Employee numbers to change them? There is an easier method than code.

I have to assume that the field from your table you want to update is "Employee Number"

If so, after you have built your table you can apply a constant number with a query. Create a query based on your table. After you have the relevant fields from your table in the query add the following into the next blank field in your query

New Employee Number:[Employee Number] +10

This will create a new field in your query called "New Employee Number", with a constant of 10 added to the old numbers, is this what you where after?

 

[Wulf13]

Sorry its been awhile. Currently the program we have will take the individuals name out of the system if they are no longer required and it will print us a new paper copy and start itself over again.

The employee number format is similiar to 01A01, 01A02, 01A03.... If bob had 01A02 this year but next year he's gone I need for whoever had the one below him 01A03 to now have 01A02 and this goes through a list of about 1200 people so you can see why I need it automated.

 

[TheEvilBankers]

Not to mention you are creating a nightmare for record keeping.

I am in the process of resigning our Associate db in my unit. We have time card numbers, teller numbers, numbers for this, numbers for that. You get my drift. I have the db assigning everyone a single number that does not change and then they can change all the other numbers to their hearts content. But they are not changing actual record order.

Problem I forsee with you changing the main number if you have it linked to anything else you are going to have to make the changes there. This can get very ugly very quickly depending on how much has to be changed. Something I have run into of late.

K

 

[Wulf13]

Here's my problem

We have a database now that was made back in 93 in DOS. It tracks "index numbers". These index numbers (01A01, 01A02 ...)are actually references to the actual "employee number". Every year our index numbers stay the same but the "employee number" changes. Mind you the "employee number" itself can't be in the program because for lack of better words, its classified so we use it's index number. For an unspecified reason, periodically a persons "employee number" is compromised so we issue them a new "employee number", with this comes a new index number. So one person could have several index numbers but only one would be active at a time. I've got what I believe to be an effective database for assigning the every member a index number. Hence the original question being, should I have multiple tables or just one??? Now here's where my newest problem is

Say it's Oct 1, our master list of "employee numbers" is new. I need to be able to hit a button that will go through and find all of "Bob's" suspended numbers and erase his name and information. Than I need to sort the remaining names alphabetically and re-assign index numbers

(Before)
01A01 - Bob - Suspended
01A02 - Joe
01A03 - Mike
01A04 - Billy

(After)
01A01 - Billy
01A02 - Bob
01A03 - Joe
01A04 - Mike

I know this sounds like a pain but if someone wrote a DOS program to do it back in the 93, I figured Access should be able to handle it. Let me know what you think? I'm getting desperate trying to figure this out. Thanks in advance for the help.

 

[Wulf13]

SO it can be done??

Thank you for your help Mr. Hartman you've been wonderful. I'm not familiar with code in Access YET, but I thought my answer might lie there. Now that I know it does, I shall pursue the challenge of educating myself in VB code for Access. I wasn't sure if VB code would allow me to make a new table. So I should be able to do this with 2 tables right? One containing the index number listing and the other containing the names/misc. info right??