Tracking depleting widgets

[Timoty]

If someone could help me wrap my head around this I would greatly appreciate it. Even though it is not widgets I am dealing with, the idea is still the same. I need to keep track of widgets and who they are issued to. The widgets come in lots. I want to have a table that keeps track of the ‘lots’ of widgets and how many are left at any given time. The widgets are assigned to people and if some of the widgets are not used they can be returned back to the supply. If a ‘lot' of widgets is defective, I need to know who received any widgets from that lot number so that I can recall those items.

Therefore I need a table for widgets that has the lot#, the original quantity, and the balance on hand. My master table needs to have a field for each employee that has the lot#, and the amount received. When I assign a number of widgets from the widget table in my employee form, I need the amount received to not only be assigned to the employee, but I also need the information to be updated in my widget table so that the balance of widgets is accurately reflected there.

This way, if the widgets are running out, more can be ordered, if some are returned to the lot number, the supply of that lot number will go back up, and if a lot of widgets is defective, I can easily check to see the various employees who have portions of that lot number, and how many they have.

If that is easy to resolve, some employees may end up with widgets from more than one lot.

If you want to make this more interesting for yourself, the widgets are actually bullets =-)

 

[Timoty]

90 percent done.

OK so here is a sloppy example of my solution so far. I still haven't figured out how to account for different employees having different widgets from different lot #'s.

 

[CEH]

First, you need to change a few things and add a few more.... You have duplicate fields in tables.... not needed... and "bal" is not needed... this would be a calculated field. Further more you need a third table... So... back to "First" do some searching on "Normalization".... Then look at some sample DB's dealing with inventory.... You are really dealing with the same thing... Think of your Officers as Customers and the bullets being the items sold... or "assigned" Also... expand on your field descriptions.... "ID" alone can become VERY confusing! Use "OfiicerID", "OfficerName" ..... After you look at a few inventory DB's you will also see you need a third table.... Something like "tblLotIssue" that will join the your other 2 tables....several other tables you will want to add also.
But again.......Look into Inventory DB's and things might make more sense.

 

[Timoty]

Thanks

Thanks, I will do some more research using your suggested criteria.