Solved Subtraction operation in query (1 Viewer)

Koloss1

New member
Local time
Today, 19:40
Joined
Jan 31, 2020
Messages
7
Hello,

I have a query in which I subtract values from two tables.

Table magacin_CLT_ulaz and magacin_CLT_izlaz

The first table is the entry of goods into the warehouse, the second is the exit of goods.
These are building elements and common values can be length, width, height. Of course, the quantity is different.
In the query I tried to subtract the quantities of goods that came in and the quantity of goods that came out.

SQL:
SELECT magacin_CLT_ulaz.kolicina AS A, magacin_CLT_izlaz.kolicina AS B, [A]-[B] AS Expr1
FROM magacin_CLT_ulaz INNER JOIN magacin_CLT_izlaz ON (magacin_CLT_ulaz.debljina = magacin_CLT_izlaz.debljina) AND (magacin_CLT_ulaz.sirina = magacin_CLT_izlaz.sirina) AND (magacin_CLT_ulaz.duzina = magacin_CLT_izlaz.duzina);

I get nothing as a result. Where am I wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
Since your query does not have a WHERE Clause, if you didn't get any records back, it would mean non of the records match with the JOIN fields you used. If so, you might double-check the data.

Also, why do you have two separate tables storing the same thing? Just curious... A typical approach is to use one table where incoming is entered as a positive number and outgoing is entered as a negative number.
 

Koloss1

New member
Local time
Today, 19:40
Joined
Jan 31, 2020
Messages
7
The problem is that I would have to get a result because the entries match.
My answer is because of the stupidity.
It's logical what you said....
But how do I say "enter value as negative number"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
The problem is that I would have to get a result because the entries match.
My answer is because of the stupidity.
It's logical what you said....
But how do I say "enter value as negative number"
How are you entering the data now? You either manually (or automatically) assign a negative number or add a field to designate whether the record is "incoming" or "outgoing." You can then use that "category" field to perform your calculations/queries.
 

Koloss1

New member
Local time
Today, 19:40
Joined
Jan 31, 2020
Messages
7
How are you entering the data now? You either manually (or automatically) assign a negative number or add a field to designate whether the record is "incoming" or "outgoing." You can then use that "category" field to perform your calculations/queries.
Maybe i can use this:

Code:
Me!TextboxR = 0 - Me!TeaxtboxR
or
Code:
Me!TextboxR = Me!TextboxR * -1
after update event.

Data are entered via the form and the command button.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
Maybe i can use this:

Code:
Me!TextboxR = 0 - Me!TeaxtboxR
or
Code:
Me!TextboxR = Me!TextboxR * -1
after update event.

Data are entered via the form and the command button.
Right. Actually, I would recommend using the *-1.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:40
Joined
Jan 23, 2006
Messages
15,364
You are welcome. Happy to help.
 

Users who are viewing this thread

Top Bottom