How can I subtract numeric data from two database table in Microsoft Access?
Dear Friend
I have two tables in Microsoft Access. Two tables has got common field named as “item“. One has field purchaseqty & another has field supplyqty and numeric data is there. Can I subtract row wise data from two tables and store it to another table which has got field item & qty?
Please help.
Regards
Pervez
It is never recommended to store dynamic information in a database. By that I mean if any of your purchaseqty in table 1 changes, or supplyqty in table 2 changed then your value stored in table 3 would be incorrect…
It is better, instead of storing calculated data, to make the necessary calculations only when you need to display it to the end user. Whether that be on a form, a report, or in a data access page.
Control Source for the calculated data would be:
=[table 1].[purchaseqty] – [table 2].[supplyqty]
OR
=[table 2].[supplyqty] – [table 1].[purchaseqty]
Whichever is suitable for your calculation.
December 27th, 2009 at 12:55 pm
It is never recommended to store dynamic information in a database. By that I mean if any of your purchaseqty in table 1 changes, or supplyqty in table 2 changed then your value stored in table 3 would be incorrect…
It is better, instead of storing calculated data, to make the necessary calculations only when you need to display it to the end user. Whether that be on a form, a report, or in a data access page.
Control Source for the calculated data would be:
=[table 1].[purchaseqty] – [table 2].[supplyqty]
OR
=[table 2].[supplyqty] – [table 1].[purchaseqty]
Whichever is suitable for your calculation.
References :