Distribute Acccess Records Evenly

You wouldn't use eight tables for this. Use a query and the autonumber:

Select [ID] Mod [PeopleCount]+1 AS EmployeeNo, *
From tblYourTable

For a PeopleCount of, say, 4, it will return the records with EmployeeNo from 1 to 4.
Filter on EmployeeNo to obtain the list for each employee.

/gustav

List 8 records of original table.
List the output tables expected assuming 3 required tables.

You dont seem to understand the requirements. These are work tables that users will use to fix records. Usually we use 4 sets of work tables b1 thru 4 and t1 thru 4. However now in our busy season we add temps so we could have 4 thru 8 people working the work table depending on the number of records that come each day could be 200 recs could be 2000 recs and i need to have a button that allows a processor to say how many tables will be needed to be worked and for that button to divide those recs among those work tables.

Then this is a tiny bit beyond the "single" question format, that this site is designed around.
This is also a lot more that an simple "function"
;-)

I can get you close...If you are willing to wait a day or so... (I'll come up with a sample db)

In the mean time, the other two experts might have something better, ...faster...

;-)

JeffCoachman

There is no convincing reason to have eight tables when one table and one query with two parameters, PeopleCount and EmployeeNo, will do.

/gustav

Grizbear51,
I see gustav's point.

So lets clarify before I/we start

Is your goal to end up with something like this:
Records:
1
2
3
4
5
6
7

Employees, Records
101, 1
101, 2
101, 3
102, 4
102, 5
102, 6
102, 7

In other words, graphically show us what you have,  and show us a clear graphical example of the exact output to want.

Monday                               Tuesday      
               Bhold      Thold                         Bhold      Thold
               1023      1023                          3021      3021
               5 Workers avail                         7 Workers avail      
              Processor types in 5                Processor types in 7      
B1-T1      205      205                                         432      432
B2-T2      205      205                                         432      432
B3-T3      205      205                                         432      432
B4-T4      205      205                                        432      432
B5-T5      203      203                                        432      432
B6-T6      0      0                                        432      432
B7-T7      0      0                                         429      429
B8-T8      0      0                                          0              0
Does this make sense? Any given day I can have any number of records.And the processor will be told how many people will be working that day and to divide it up that many times.
I hope this copies over right. If I can just get how to divide up the one set of tables I can use the seq number to match them to the other buti dont know how I can divide them evenly and then make sure the odd number at the end is done.

http:#a39590481

If you use my method, it would leave distributions of:

205
205
205
204
204

and:

432
432
432
432
431
431
431

Wouldn't that be the optimum?

And, still, I cannot se any purpose of having eight tables for this.

/gustav

OK Gustav lets try your method when i paste the code in the query -

Select [ID] Mod [PeopleCount]+1 AS EmployeeNo, *
From BEQ_File_Hold

I am assuming when it asks for  [PeopleCount]  thats the 1 thru 8 divisor I put in
what is the  [ID] that Im expected to type in
and where does this auto number come from or into?

> .. [PeopleCount]  thats the 1 thru 8 divisor I put in

Yes.
ID is not to be typed in; that's the name of the primary key field of type AutoNumber.

The returned EmployeeNo is - for each task - the assigned employee number - between 1 and PeopleCount.
To obtain the list of assigned tasks for, say, employee number 3, filter the output on:

    EmployeeNo = 3

That's how I see one query will replace eight tables.

Also note, that at any given time if the PeopleCount changes, just run the query again.

/gustav

Well I'm very confused by what I'm suppose to be getting here.
I put in the code in the query

Select [ID] Mod [PeopleCount]+1 AS EmployeeNo, *
From BEQ_File_Hold

the BEQ_File_Hold has 168 records in it
I run it and it asks for ID info but I put nothing in and click OK as you stated
It then asks for PeopleCount - since i would like the records divided 4 ways for 4 workers
I put in the number 4
It simply brings up all 168 records with a field EmployeeNo blank
you say filter on employeeNo but Ihave no such field and how will this divide 168 records into 4 sections?

wow now I understand what your doing. This will definitely work, I will have to do a lil adjustment here and there but much simplier than the function and code I had envisioned. This is a brilliant way to divide up the data within a table then you can distribute it as you wish. Once agin I have been awed by this sites "very smart people" thank you.

This was a brilliant solution

Thanks!

/gustav