473,224 Members | 1,446 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Subtract records from two different tables

1 Bit
I have the following tables, with their fields.
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

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
Jul 14 '22 #1
1 5604
32,554 Expert Mod 16PB
This seems to be a strange question.

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.
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.
Jul 15 '22 #2

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

Similar topics

by: manning_news | last post by:
Using SQL2000. How do I format my select statement to choose one out of 24 different tables? Each table is slightly different and I was hoping I could use one select statement and format it...
by: Paolo | last post by:
I know I should not be doing this, but I find it very useful. I have a database in Access which stores data for a small company. Sometimes we need to add similar information to different tables....
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have...
by: Dennis Gearon | last post by:
I am working on a design where a location for something can have: Fully qualified address with even building names, room numbers, and booth numbers. **-OR-** GPS location **-OR-** Both ...
by: apgoh | last post by:
Our system is on AIX5.3 ML5, DB2 v8.2 FP11, and we are running C applications. There are 2 scheduled cronjobs, which are actually triggering the same binary, but passing in different parameter....
by: lieufirst | last post by:
hi there, well i have a db that is consists of 6 tables. 1 for the detailed information of students, 1 for the exams they should take another table that combine student name and class and id from...
by: HaLo2FrEeEk | last post by:
I have two different tables which havea different number of columns. One of the tables gets a new row every day and is populated with a st of 4 IDs from another table, along with today's date and an...
by: PreethiGowri | last post by:
I want to subtract 2 column of two different tables and update in one of the table say, i have two tables 'main' and 'sub' both tables have a column named 'quantity' so i have to do it in this way:...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.