Allocator - Access VBA

[santoshuhg]

Hi

Can some one help me with the code to allocate inventory equally

Inventory Table:

Group Number - Work - Allocated To(Person Name)

Reference Table:

Group Number1 - Person1-Person2 - Person3 - Person4
Group Number2 - Person2-Person4 - Person5 - Person6
Group Number3 - Person1-Person3 - Person5 - Person6


Code required:

Inventory table has fresh inventory where few person names already exists
Reference table has list of group numbers and person authorized to process that group
Allocation should be done in Inventory table with equally to all (Existing+Fresh) Number should be equal.

Hope I am able to explain the logic.

Kindly help... please guide how to do & functions that need to be used if entire code can't be provided.

Thanks in advance​

 

[Mihail]

Welcome to the forum.

I don't understand what you have.
Post your DB. (Access 2003 and ZIP).

 

[santoshuhg]

Hi Mihail

It's access 2010 file & the database is very heavy to zip & upload.

I have numerous tables & queries out there... I am stuck only allocating inventory equally when rollover exists for an employee.

The code I have written can allocate values equally but don't make the rollover (Existing ones) uniform

let me try to explain it once more

There is Inventory table where today work is present.
"Group Numbers" - "Work" - "Allocated To" these are the columns

I need to assign the work in "Allocated To" field by refering to "Group Numbers"

Now few fields (Allocated To) in this table have alraedy individuals name coz the inventory has rollover means that work was already allocated to those individuals yesterday.

Now whatever is fresh I need to allocate it equally to all and I have that code which do that.

Problem is that I need the number equal keeping the rollovers as it is.
e.g

Employee 1 has 20 rollovers
Employee 2 has 10 rollovers
Employee 3 has 5 rollovers

And fresh inventory is let's say 40

Then after allocation it should look like this

Employee 1 = 20+5 = 25
Employee 2 = 10+15 = 25
Employee 3 = 5 +20 = 25

It means code should decide that how much work should go to each employee according to their rollovers.

What I have: I have macro which allocate work equally it means acoording to my macro

Employee 1 = 20+14 = 34
Employee 2 = 10+13 = 23
Employee 3 = 5 +13 = 18

It will just divide fresh inventory among three equally.

Her is what I thought if I have code which can identify highest value of rollover and then make others equal to highest value & then allocate remaining eqaully that will work.

But I am stuck writing this code... please help

 

[pr2-eugin]

Hi Mihail

It's access 2010 file & the database is very heavy to zip & upload.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Mihail]

In order to convert your DB to a previous version:
Press Office button.
Save As...Access 2002-2003

 

[santoshuhg]

Here is the file ... I have removed unnecessary data

Allocated inventory is supplied with data.

Main form - click Apply formats & it will pick rollovers from old inventory tables

Allocate inventory take reference for CFR mapping tables & allocate inventory equally basis group numbers

but this is done only for Fresh Inventory

I want rollovers & Fresh allocation should be equal for people in same group

I have used three group numbers & 6 Names which are assigned to process work

Kindly help

 

[santoshuhg]

Sorry I posted accdb file... you can save in 2003 version and send ... else please let me know if I need to resubmit

 

[Cronk]

It appears the inventory is allocated to a Group and each Group comprises a number of people, with any particular person assigned to any number of Groups.

If Group 101 has 20 items of inventory and 5 people in that Group, does that mean each person is allocated 20 or is allocated 4 units?

The algorithm for allocating new inventory directly to people so the number per person was equal after allocation, would be to
(1) sum the inventory on hand
(2) add the new inventory received
(3) divide that sum by the number of people to get the number per person after allocation
(4) subtract for each person, the number they currently have from the number in (3)

 

[santoshuhg]

Hi Cronk

Thanks for the input & I am on same page to calculate

Can you help me with the code.

Thanks in advance.

 

[Cronk]

Yes, I can help. What is your specific question?

 

[santoshuhg]

If you look into the code I have written under allocate button.
This equally divide the inevntory group wise.

Can you please add the above algorithm so that it include rollover count as well & then distribute equally

 

[Cronk]

What is the answer to my question in #8?

It makes a difference.

 

[santoshuhg]

If Group 101 has 20 items of inventory and 5 people in that Group, does that mean each person is allocated 20 or is allocated 4 units?

Answer: if all 5 people in that group doesn't have nay existing inventory then yes 4 each would be the allocation.

But if let say 5 people in group 101 has inventory already then macro should pick the hishest value among the 5 people then bring all 5 people inevntory equal & then allocate remaining equally

e.g.

Existing inventory with those 5 people are as below and fresh inventory is 20

Employee1 - 0
Employee2 - 2
Employee3 - 0
Employee4 - 2
Employee5 - 1

Then the fresh should like this

Employee1 - 0 +5 = 5
Employee2 - 2 +3 = 5
Employee3 - 0 +5 = 5
Employee4 - 2 +3 = 5
Employee5 - 1 +4 = 5

Looking for your help.

 

[Cronk]

So the quantity of inventory Employee1 has in Group 2 does not matter?

Have you thought your whole system through? I don't like it when the same data entity has different names in different tables. Employee information is held in fields called AllocatedTo, TPXID, CFR in different tables.

Rather than Employee names, Employee indexes would be better.

How does inventory become "Old Inventory"?

How does the inventory first get allocated to a particular group?