Auto populate information in table 2 when updates occur in table 1

[Johnnyjones]

I have two tables. One user table and one ID table. The ID table is fairly static. What i want to do is add a user to the user table and then automatically assign the first available ID from the the ID table. When a user is deleted from the user table, I want to clear the ID that the user is using and make it available for new users as they are entered in the system.
Some additional information. ID's must be assigned based on select criteria (groups). Each ID is assigned to a specific group. I don't think the criteria are the important part (but I could be wrong). What I'm not understanding is how to automate the assignment/update portion. Thanks in advance.

[twinnyfo]

Johnny,

I may be a little confused by your language. In my world when we talk about "ID", we typically mean the primary Key or a unique record identifier that makes each record unique. If this is what you mean, then I would ask why you must keep the same ID for new records.

If this is not what you mean, but that "ID" is merely some form of identifier that is specific to a particular group in your administration, then that is another thing.

If it is the latter, then your code would search for the first record assigned to the group the user is assigned to which has no user associated with that ID, then assign that user the ID in the user table and assign that ID that user in the ID table.

However, this is just a rough guess, as I don't know how your tables are aldi out and what other criteria you are using to find these IDs.

[Seth Schrock]

Some more details about the ID are needed. What makes it the first available one? Numerical order, date it was made available, etc. Also, we need to know what the fields are in each table.

[Johnnyjones]

Hi. First - thanks for the quick replies and helping me understand what I'm missing. Let me see if I can elaborate. The user table uses an employee number as the primary key. The ID table contains a static list of badge ID's. We want to automatically assign a badge ID to new employees in a 1:1 relationship. I am thinking that the E# becomes a foreign key in the Badge ID table. The badge ID's should be recycled to the end of the assignment list such that a badge ID unassigned today is placed as the last badge ID when a new assignment occurs. Please forgive the newbie questions - willing to bet this is easy.

[Johnnyjones]

To confirm... It's the automatic part I'm struggling with...

[twinnyfo]

You are correct on the Employee number beinga Foreign key--your already getting the hang of it.

You could add a date field to the Badge ID Table, then look for the oldest available badge.

To help, we kinda need to know how you are working your badge assignments. There is not much to go on. Are you using a form? Have you built anything that we can troubleshoot yet?

[Johnnyjones]

Right now I have the basics of the tables set up. I don't have a form built at this point as one additional thing ( that I likely should have mentioned) is that we want to complete the inputs in bulk. The plan was to create our tables and house them in a sharepoint list. It's easy to copy and paste in employee numbers into the user fields. Do I need a form to do this? Do I need to write code that checks each employee for an assigned badge if I go the bulk method?
I apologize in that I feel like I'm asking the wrong questions because I know so little.
Would I have to have VB written to populate the foreign keys automatically? I'm just not sure where to even start the research on this.

[twinnyfo]

Your first question implies that you are not batch updating, but updating on an as needed basis:

When a user is deleted from the user table, I want to clear the ID that the user is using and make it available for new users as they are entered in the system.
Some additional information. ID's must be assigned based on select criteria (groups). Each ID is assigned to a specific group. I don't think the criteria are the important part (but I could be wrong). What I'm not understanding is how to automate the assignment/update portion.

I guess I am confused on the batch update issue....