Allocate quantity per team based on member count

[critusodem]

Objective:


Have a DB which allows for the inventory input to be evenly distributed to each member and then generate a report for each team, passing on the allocation to the members within each team

Tables:
tblInventory
IDinventory (AutoNumber)
ItemName (Short Text)
Quanity (Number)

tblMembers
IDmembers (AutoNumber)
Member Name (LookupShort Text)
Team (Lookup using Team Name from tblTeams - Short Text)
tblTeams
IDteams
TeamName

Example of the resulting report:



Note: The quantity fields above are not accurate, only displaying the total quantity from the inventory. I just populated the fields so there is some data being displayed.


My request is that someone show me how to populate the fields for each team to reflect the quantity that would represent the allocation to each team based on the number of team members within each team.



Here is a summary of the process the non-profit org uses currently:
Truck arrives with pallets of donated goods
The sum total of goods are inventoried
The quantity is divided equally using the total number of members
The amount allocated to each team is based on the total number of members within each team
Take the resulting data and complete a distribution sheet for each team
This is done for on average 22 teams
This is done at a minimum 2 times a week!
Except for the printing of the blank distribution form, this is all done by hand. In other words, at each step, the data is collected and filled out by a person with a pen.


I am trying to help them remove the plethora of hours of hand writing out the information. I wager they will tear with bliss if we can automate this down to the point of simply needing to input the inventory into the DB and printing out the distro sheets.

[Micron]

Your form will need the textbox controls to determine the quantities (as you've shown) plus you need calculated controls to determine the allocations. You should disable or lock the commodity total controls so the values cannot be edited (you should use a separate form for handling the receipts and disbursements to arrive at the quantities). If I get the drift, the calculated controls would divide the sum of carrots by the grand total of members (70/6=11.66) to allocate 11 to each member. I don't know what you want to do with the other 4 carrots remaining. If you're printing this and already have the query designed for the form, I'd consider using it to designing a report and printing that instead. Forms can be printed, but they are very much lacking in the quality of the output and power that can be harnessed by a report. I "see" a report with the commodity totals in the header (no need to print the count of a commodity for each team) with details grouped by team. The calculated controls in the team groupings refer to the commodity totals in the header and simply report the commodity count distribution per team.
If you go the report route, you can turn this form into one that can handle the receipts/disbursements, or just start over. Lookup fields are for editing inside the tables themselves, which you really should avoid. Use forms with controls instead.
Hope that helps!

[orange]

I'm not sure I understand the post, but my first thoughts are:

ReceiveDonatedGoods--->reviewed and Inventoried by Somone(s)---->Inventory/Quantity By Category (A)

Team-->TeamMembersCnt(B)

Distribution of DonatedGoods category by TeamMember = A/B

Good luck.

Cross posted http://www.utteraccess.com/forum/ind...ic=2038192&hl=


Please see this material re cross posting.


When you cross post, place a link and message to readers that you have done so.

[Bulzie]

I assume each time this is done, all items are distributed so you have no left overs? Each new inventory session starts over? Maybe this:

Table tblItem - Master list of all items. Add to this list if a new item not in table. You will use this in a combo box when you do the inventory.
ItemID (autonumber)
ItemName
Itemgroup - (bag, each, Gallon, etc.)

Table tblMember
MemberID (autonumber)
MemberName
TeamID

Table tbTeam
TeamID (autonumber)
TeamName

Table tblInventory
InventoryID (Autonumber)
InventoryDate - This will be unique to this specific Inventory session. Use same date when recording all the items for this session.
InventoryComplete - Y/N - After you distribute the items from this session, you mark all these records Y so that you don't include these same items in the next inventory session
ItemID - select this from a combo box
ItemQuantity

Table tblDistribution
DistributionID (Autonumber)
InventoryDate
MemberID
ItemID
ItemQuantity - based on number of members

Once you have the inventory recorded, write code to loop through the items in tblInventory where InventoryComplete flag is N and based on total number of members, distribute the amounts into tblDistribution and mark the items in tblInventory to InventoryComplete to "Y". Use MemberID to get TeamID and link back to tblTeams and ItemID to link back to tblItems to get the names and Item amounts for your reports.

[critusodem]

Thank you all for taking the time to respond, so quick to respond as well!
After reading all of your input, it literally had me assess what it was that I am trying to do and how to reach the solution.

I am still novice enough that explaining more than just that it works after your help is all I can provide as a response without a headache.

Now, there is only one remaining issue, which I will humbly pester the forum with shortly.
So again, just by your replies content, it expanded my comprehension of how to create with MS Access, danke!