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

 

[tmd_63]

yes. you appear to be on the right track

 

[tmd_63]

next look at the relations between the various tables (how the itemID's are recorded etc).
think about what to want to input or display on the forms and reports then create the relationships, create queries which match the input from the tables, calculations and output.
Then create the forms using the queries.