471,045 Members | 917 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,045 software developers and data experts.

Subtract records from two different tables

1 Bit
I have the following tables, with their fields.
TblSales
Saleid As auto Number
Saledate As Date
Item As Text
Brand As text
Quantity As Number
Unticost As currency
Totalcost As currency
Stockid As text

TblStock
Stockid As Text
StockName As Text
Brand As Text
Totalstock As Number
Stockremain As Number

How can I subtract sales Quantity from the Totalstock for every transaction

Am having too many logical errors Please someone help me

Thanks in advance
3 Weeks Ago #1
1 5030
NeoPa
32,336 Expert Mod 16PB
This seems to be a strange question.

Fomson:
How can I subtract sales Quantity from the Totalstock for every transaction?
It's almost like you think it makes any sense to do so in a report (or SELECT query). How could it? The [Totalstock] value would be the same for each associated transaction. Consider that you might have a number of items in stock starting at 100 then twenty different transactions of four each (adding up to eighty). Any such report would show ninety-six for each line.

No. You have to be clear about what each value means. Is it stock as at some time in the past? Is it ever updated? Which of the transaction items have already been applied to the stock levels so should not be counted again?

It actually is possible to get a reported value of stock items left using the original value and subtracting all relevant transactions, but as we've seen, this is not simple or straightforward. In practical terms most systems are designed so it is not required to process through all historical transactions (+ve & -ve) in order to determine the current status.
Fomson:
Am having too many logical errors Please someone help me
We can only really do that properly if/when you share with us some details of what you're trying to do and exactly what you're getting stuck on.
3 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by manning_news | last post: by
20 posts views Thread by Dennis Gearon | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.