Assigning text value based on numbers. (1 Viewer)

Let me start out by saying my company has a non disclosure so I can not upload anything.

I am hoping I am just over complicating this (which I tend to do.)
Here's my issue. I have a query that pulls all the "loads" for that day with a description of the load and divides the pallets by zone in the warehouse. (Ex. Load 1 has 5 pallets in zone 1, 3 pallets in zone 2, and 2 pallets in zone 3. Although some loads have null values) As well as having a total number of pallets. (Load 1 has 10 pallets).

All the above is working. This is what I am stuck on.

I need to take the information it pulls in and have it randomly but evenly assign it to a user inputted amount of workers (due to call outs and vacations) based on zone and pallet count.

For example 100 pallets total between all loads. We have 4 workers that day each person gets 25 pallets.

Here is how the query looks. (Note: the zone with the highest number beneath islt is where the load is placed)

Load name Zone 1 zone 2. Zone 3. Total. Worker


Load Z. 2. 5. 3. 10. A

Load X. 8. "" 2. 10. A

Load Y. "" 5. "" 5. A

Load U. 25. "" "" 25. B

Load W. "" "" 25. 25. C

Load P. 13. "" 12. 25. D

Total for day. 100

Any help is greatly appreciated. Again I am missing the worker column in the above example. ("" = blank space)

Thanks ahead of time. And sorry for formatting issues I am on my phone.

I suggest you start with a description of your business (you don't have to give out company secrets).
We have a warehouse from which we deliver loads of pallets to customers? There may be x pallets per load? We have employees who deliver the "loads". ......

A few examples with some realistic data would help readers understand what you are trying to support with a database.

It's important to tell us clearly what you are trying to accomplish.

We have loads delivered to us from hundreds of vendors, with several different appointments spaced about an hour apart, with varying amounts of pallets per load. Some loads have 1 pallet, some loads have 90 and everything in between. The different zones have different items in each, however, one load can contain product from 1 zone, 2 zones, or all 3. (I have all this information already in a query)

Our employees verify the freight that comes in on every load.

As far as appointments go the amount of loads for each
appointment varies as well but each worker needs to have at least 1 load from each appointment. Which we always have more than enough per appointment to accomplish this.

We average around 1200 pallets coming in on a daily basis (This varies day by day).

This query is ran the day before and the day of. It shows the load name, amount of pallets per zone, and total amount of pallets over all zones combined. If a majority of the pallets are going into zone one that is where we put the load same with zone 2 and 3.

What I am trying to do is take the information from that query and have access assign workers to individual loads based on pallet count.

(I do have some experience with modifying VBA if need be)

For example we have 1200 pallets coming in. We have 4 workers it assigns them 300 pallets each based on total amount of pallets coming on each load.(that is what I can not figure out how to do) I would prefer to keep the workers in 1 zone primarily and then move to the next of they still need loads to hit their quota for the day. (If this is not possible that is fine)

The most workers we will have in one day is 7 the least is 1. Again I need to be able to input the amount of workers manually due to call outs vacations and other circumstances.

I apologize for the miscommunication I hope this clears this up.

You know exactly what you are dealing with. I'm a little slow since I am not in the business and know little about your set up.
For clarity:

You say you have loads delivered to you. Do your employees deliver/transport these to customers?
What exactly is a zone? Sounds like a location/region.
Appointment?

If you receive 100 pallets, and these are destined for zone 1 (60 pallets) and zone 3(40 pallets) what happens?

It sounds like pallets should ideally be sorted by zone?? but it isn't clear if that's even possible.

A zone is a section of the warehouse in relation to the items on that order. The only thing that happens when a truck contains loads for multiple zones in our forklift drivers have to travel different lengths.

Basically without to much detail...
1 loads come in..
2 workers verify the load...
3 forklift drivers put the loads away..
4 pickers pick customer specified orders
5 we ship those orders out

The loads each have an appointment window
4
5
6
Etc.

We may have 10 loads scheduled to come in at 4 and then another 15 at 5. This gives us enough time to cycle through the 4 o clock loads and start on the 5 o clock loads.

We can not really sort the incoming pallets by zone because they are coming from outside vendors. For example samsung makes washers dryers and phones and TV's. Zone 1 is for appliances zone 2 is for phones and zone 3 is for tvs. One truck may contain a tv and 2 washers. We would put that in zone 1 because better to have to transport 1 pallet to a different part of the warehouse then 2.

For this queries purposes our employees do nothing more than verify the load.

Just to make sure, you have a file structure similar to the following?

PARENT FILE
T_Delivery
ID Primary Key
Bay Where unloaded
Dt_Delivery Date and time of delivery
Ct_Pallets Total of pallets

CHILD FILE
T_Breakdown
ID PK
ID_Delivery Parent ID
Zone Location pallets are going to
Ct_Pallets Total count of pallets going to THIS zone for THIS delivery.

If so, you should be able to total by zone by delivery time to give you your totals. Do you need to then assign individual pallets to operators or are you simply concerned how many they move?

Easiest way i can think to say what I am trying to accomplish

I have a set of random numbers that equals 100

10
20
15
10
20
5
5
15

How do I have access divde that number by a user inputted number for this sake we will say 4.

Which is 25.

Then assign A to 25 ,B to 25 ,C to 25 , and D to 25. Like so

10 A
20 B
15 C
10 C
20 D
5 B
5 D
15 A

Or if it were 3 do ABC or 2 AB or 1 A... etc.

Mark... that is the basic break down yes . I need to assign operators to pallets. And have it be semi even. Like the example above.

The reason for this we have workers not carrying their weight so we are dividing the work up evenly and distributing it. Since all loads have varying amounts of pallets it would be very much a time saver using automation to distribute the work.

In pseudocode, what you would be looking for is similar to the following


I don't have your file layout or how you assign to workers, so you will have to fill in those bits.

Noticed I missed something as I was typing...

Thank you looks good I'll give it at shot and report back

Here is a model of your set up based on your post # 3