How to pass 2 tables data to third table?

[Anderlt]

Hello. I have to tables, that I want to form the third table from their data. So one table is for the quantity of products that were made to be entered and the other is for taken quantity out of stock.


Entered quantity table:



Taken quantity out of stock:




THIRF TABLE DATA ( The table I want to see):

[pbaldy]

Where to start. You shouldn't have a table with that data, it should be dynamically calculated in a query. I hope you don't have a set of tables for each product, that would be a big design mistake (search on normalization). Spaces and symbols in object names are more trouble than they're worth in the long run.

Given that the fields are the same, I personally would have a single table with negative amounts for the "taken" records. Then you can use a simple sum to get the balance. As you have it, you have to join the two tables together in a query. Presumably you'd also have a products table, which could also be joined so that you see all products, whether they have records in both tables or not. Search on running sum or running total to see how to get the currently in stock column.

My fingers are tired, let's start there.

[ArviLaanemets]

About table design:
Date is reserved word - use e.g. EntryDate instead;
Instead employee name in various tables have a table where all employees are registered. Every employee in this table must have an unique ID which is used as foreign key I other tables. User selects employee in any form in combobox - user ID is stored, but his/her name is displayed;
Instead product name have a products table with unique product ID. Rest is same as with employees;
Your tables contain storage transactions really. Like pbaldy adviced, you better have all transactions in single table, and have there a field which determines transaction type. Simplest design will have only 2 types (in and out). Entry quantity for type 'in' will be in all calculations positive, for type 'out' negative. More advanced design has a table for transaction types, where for every type a multiplier (1 or -1) is saved. This design allows you to differ between purchases, sales, products given out or returned, scraping, inventory corrections, etc.

Current saldo of product in storage will be sum of (all entries multiplied with movement type multiplier). Saldo at certain date will be sum of (all entries with date equal or less of query date multiplied with movement type multiplier).