General - Divide records and add User Name

Hi!

I don't know if this can be done, but I am importing a data set into my Access database. I would like to divide these records and add an employee name next to the record so that each person knows which records they are responsible for. For example, if there is twenty records and I have four employees it would be divided into
records 1-5 Employee 1
records 6-10 Employee 2
Records 11-15 Employee 3
Records 16-20 Employee 4

And the employee name would be inserted into the table that contains the records (I then have a form set so that each person can search for their records). Sometimes there will be 1000 records, sometimes 100 so the numbers are not set.

Any help or ideas with this is greatly appreciated! Thanks!

How will you determine which records should be marked for which employee? It's simple to add a new field to a table, just open the table in Design View and add a new field at the bottom, then save it. The issue is how you intend to enter employee names. In fact, you really shouldn't use their names, because if you have a typo some day, you'll start losing records. The right way to do it is to have another table with employee names and a primary key. It's the key value that should be stored in the new field in the other table.

So if you will indicate how you're going to establish which employee should handle which records, we can probably help you.

That's what I was thinking of doing. For example, "Rob" would be 1, "Jim" 2, etc. up to all of the employees (lets say 4 employees) and have these numbers act as the primary key in a different table that contains the names. It doesn't matter which employee gets which record, just that the records are divided nearly equally amongst the employees. Maybe a code that inserts a number into the new field in the table by counting 1..2..3..4 and then starting over after 4 rather than actually dividing since the records might not be able to divide exactly even. I realize this is a specific request but I'm sure it would help other people also in the future so any help is appreciated. Thanks!

OK, you're on the right track. This question is hard to give a general answer to, because the answer would be different under slightly different conditions, but assuming the following:

• You will rarely need to add or remove employees from the task; and
• You will be frequently adding new records in batches,

I would probably go about it this way:

1. Create an Employees table with the fields you described. Make the ID number the primary key by clicking on the golden key icon in the Table Design View toolbar.
2. Make the field in the other table a Number data type with a length of Long Integer.
3. Write an Import Specification or an Append Query or something to import a new data batch, without assigning anything to the employee field.
4. Write a VBA procedure to assign unassigned records in rotation, as you described, to keep the assignments balanced. Your procedure would probably need to first check on how many records are assigned to each employee and then start with the appropriate number and rotate as it reads each record. The procedure will need to open a recordset that includes all records that have not yet been assigned, step through the recordset and run a series of updates to the table based on the primary key of a record and the current status of the rotation variable, the maximum value of which you set to always be the number of records in the Employees table.
5. Create a button on a form somewhere that launches the VBA procedure when desired.

I don't have to time to write that procedure for you, but if you don't have VBA skills, perhaps someone else can, or when I have more time, I can do so.

This is what I ended up using:

Function insertnumber() As String
Dim rst As DAO.Recordset
Dim intValue As Integer
Set rst = CurrentDb.OpenRecordset("SELECT ID FROM [Filtered Data]")
intValue = 1
rst.MoveFirst
Do Until rst.EOF
If intValue > 11 Then
intValue = 1
End If
rst.Edit
rst(0) = intValue
rst.Update
rst.MoveNext
intValue = intValue + 1
Loop
End Function

Just in case anyone else needs to do something similiar. Thanks!