Originally Posted by
ArviLaanemets
It looks like your DB will differ from standard inventory DB as you have to follow the history of single item instead of history of article. I.e. the quantity of item you follow will be always 1! a possible structure for this:
tblArticles: ArticleID, ArticleType, ArticleName, ... (The table used to group various items. The AtricleTyp is used to group various articles - e.g. hardware, software, service, etc. Yuo can also have several grouping fields, to allow structured grouping like material and immaterial articles.);
tblItems: ItemID, ArticleID, SerialNumber, ...;
tblTransactions: TransactionID, ItemID, TransactionDate, TransactionType;
Probably some additional tables as registries for clients, suppliers, etc.;
Probably some additional tables for financial info like ordering, invoicing, arrival, etc.;
Probably a table where you link financial info with transactions.
You need a form to register items;
You need a subform in items form to register all transactions with active item. Transactions for given item can be of various types, like purchasing, reserving, selling, etc. The type of transaction determines, is the item added to your stock, or it is removed from it.;
And of-course you need forms for managing all other tables mentioned above. And some reports.
But those 2 forms above will be your main instrument.
The current status of any item will be determined by latest entry for it in tblTransactions, which will be your most important data depository. You can determine the status of any item at any given date in past. You can find which quantity of certain item you have/had available in stock currently/at any given date. You can calculate, how many items of given article you did purchase/sell over given time period. You can find e.g. the certain article item which is available in stock currently, and has e.g. earliest/latest arrival/purchase date of all such items. Etc.