Distribute Acccess Records Evenly

I have 2 holding tables BEQH & TSOH (access 2010 windows 7).
they will contain any variable number of recs each day -  20 to 6051 plus etc etc.
I have 8 work tables to put them in distributed evenly depending on how many people we have for the days work, (Beq1-thru8 & Tso1thr8) they have a unique identifier in both tables of a text field called "Dtlseq" this allows me to match recs. (Beq1 rec 0000001 = tso1 rec 0000001.
I want to develope a process that will ask the processor how many tables to break out
so it will prompt him with an input box and he will input 2 thru 8
this will allow the process to somehow divide the records evenly across the number of work tables to include an odd record count here and there.
I am not bad at coding so I am assuming this can't be done by queries and will require a function. Anyone with experience that can give me a start in how to accomplish this?
Stephen RoesnerAnalysisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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
0
hnasrCommented:
List 8 records of original table.
List the output tables expected assuming 3 required tables.
0
Stephen RoesnerAnalysisAuthor Commented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
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
0
Gustav BrockCIOCommented:
There is no convincing reason to have eight tables when one table and one query with two parameters, PeopleCount and EmployeeNo, will do.

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
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.
0
Stephen RoesnerAnalysisAuthor Commented:
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.
0
hnasrCommented:
0
Gustav BrockCIOCommented:
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
0
Stephen RoesnerAnalysisAuthor Commented:
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?
0
Gustav BrockCIOCommented:
> .. [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
0
Stephen RoesnerAnalysisAuthor Commented:
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?
0
Gustav BrockCIOCommented:
Well, as I wrote:

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

If you don't have an AutoNumber field in the table, create it named ID.
If you have, adjust ID in the SQL to the name of the field or rename that field to ID.

EmployeeNo is not a field of the table; the query calculates and returns that field.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen RoesnerAnalysisAuthor Commented:
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.
0
Stephen RoesnerAnalysisAuthor Commented:
This was a brilliant solution
0
Gustav BrockCIOCommented:
Thanks!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.