Table Structure - Please Guide

[ferhanz]

Dear All,

I am making a simple database in MS Access for handling the store in our office, where there will be a table1 having the items names, quantity etc and table2 for people who were issued those items and the quantity. I want to make the database in a way that whenever an item is issued to someone it should subtract that quantity from the total quantity in table1. What should be the suggested table structure for that ?

Thankyou


Farhan

 

[MarkK]

At a minimum you'll need a table in between the person and the stuff, let's call it tOrder. As long as every order only has one kind of stuff on it. If a person might show up and get different kinds of stuff in one pile then tOrder will need to be divided into bits, so you've got tOrderDetail.
Four tables and counting. tStuff, tPerson, tOrder, tOrderDetail.

tStuff
StuffID
StuffQuantity
StuffName
Price

tOrder
OrderID
PersonID
OrderNumber
OrderDate

tOrderDetail
OrderDetailID
OrderID
StuffID
Quantity
Price

tPerson
PersonID
Name

 

[ferhanz]

Thankyou langbot

Will make the tables and get back incase of any issue

Farhan

 

[gemma-the-husky]

but note that although deceptively simple, inventory/stock is noe of the hardest things to get right - no doubt you also need to deal with stock intake, and adjustments for stocktake differences, as well as stock issues.