Inventory tracking for kindergarten

[probingreality]

Hi I am trying to build a simple database for tracking inventory in a kindergarten.

Basically, the prinicipal wants to be able to:
1. Input ordered products and their amount
2. input which employee takes a product, number of products taken
3. Stock control - how many products are left (calculation of products ordered minus products taken by employees)

So far I have done this:

Employees Table: EmployeeID, Name, GroupName, EmployeeTime, Additional Notes

Products Table: ProductID, ProductName, ProductCategory, Price

Suppliers Table: SuppliersID, SuppliersName, SuppliersPhone, AdditionalDetails

ProductPurchasing table: PurchaseID, ProductID, PurchasedAmount, Date, SupplierID, AddiotionalDetails

Inventory Transaction table: InventoryTransactionID, EmployeeiD, ProductID, AmountTaken, Date, AdditionalDetails

I just wanted to ask whether I am on the right track and how to create the ability for calculation between purchased product amount and taken amount to be in a report of available inventory.

Thank you so much in advance - I just want to help the principal with this so she will not be calculating this manually. It is a volunteer project for me

 

[plog]

It looks good, however you want your product crebits (currently ProductPurchasing) and debits (currently Inventory Transaction) all in one table so that you can calculate how much you have on hand by simpling adding all the transactions up.

If you could somehow merge them that would be ideal, however they don't have the exact same fields so that could be tricky. It may require a third table simple to track product, date and quantity.

 

[probingreality]

Thanks plog, I was thinking of creating another table that could take the information from Inventory Transaction table and ProductPurchasing table but I was not sure if this was the way to go.

I am still not sure how exactly it will happen