I do not understand what you mean by "quantity of basic".
Is this someone else's database that you are trying to use as a template? I will go out on a limb and say this is not a very good design for Inventory entities. Maybe if it is intended for use over the internet. For desktop databases, it is usually a bad idea to store calculated data in the tables. I see a lot of quantity fields in various tables. This will create issues when designing your app and trying to maintain constraints/data integrity.
Having said that, newer versions of Access have many constraints that can be enforced using intrinsic features at the table layer.
Thank you, Professor
How can I manage and control inventory
Quantities that enter and quantities that come out
I use Yes/No fields at the Child table. The Parent table would be something like a customer request or an Order to bring inventory IN. A child table would describe what is in the Order/Request. There may be other child tables that break down the inventory further. Perhaps the Order/Request describes multiple trucks and or multiple pallets. Child tables will describe what is inside a given truck/pallet. Use the yes/no fields at the lowest Child to indicate if something is IN inventory or not.
The basic structure looks good to me. I can't identify any calculated fields.
To determine products on hand, do aggregate queries that summarize the tblAcqDetail and tblInvoiceDetails and tblStockTake records then join those queries to tblProducts. Do calc in query that subtracts sums of invoice details and stock take from sum of acquisitions.
Take a look at the MS Desktop Inventory database template. http://office.microsoft.com/en-us/te...010206882.aspx
Also, http://allenbrowne.com/AppInventory.html
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
If you take a look at June's link to Allen Browne's site, you will see that he explains the peril of storing a "quantity on hand" in your DB (towards the bottom of the page).
I did not look too closely at the link but, perhaps you and I are interpreting the Quantity field as a 'quantity on hand" when, in fact, it is a description of that row/record. In other words, a count of pieces in a case, where, according to the business rules, the case would never be divided. Unfortunately, I do not have the time to review what Allen has there.
Any example that I have for an inventory system would be very complex and not worth much as a model. All of my inventory DB's are a part of a larger Warehouse Management System. The models are specific to types of products and complex business rules.
The only other thing I can suggest, besides the links June offered, would be to look at the Cascading Combo example I uploaded. It is not a sample DB for an inventory system. It is a sample of various approaches to using VBA. If I recall correctly, there is some use of yes/no fields in tables. When using multiple child tables, I will violate rules of Normalization and include the Primary Key values of relative fields from ALL Parent tables in the Child tables.
https://www.accessforums.net/sample-...tml#post200624
I have not looked at that sample DB in quite a while...
Thank you very much
This is an example, where relations have been built on the light by Allen Browne