I am trying to set up a base to track asset/inventory quantities, costs, assigned employees, and locations, but am fairly new to databases and couldn’t find anything in existing topics that helped me beyond probably needing a junction table?
I currently have three tables:
- one showing the item, type, total quantity, and cost per item
- one showing the employees that currently has a column showing links to the items
- one showing the locations
I want to be able to designate how many of each item each employee has been assigned, and where the item currently is. For example say there are 5 laptops worth $500 each, John has 2 (1 at home and 1 at Office 1), Mary has 2 (1 at home and 1 at Office 2), and the last is currently unassigned but is at Office 1. This situation is then repeated with various other items with varying values.
From that, I want to be able to see at a glance that John has $2,500 worth of items and a breakdown of what those items are, while having some kind of limit that tells me, “there aren’t any more of item X available” if I try to assign units beyond what is available.
Thank you for any help.