# Thread: Problem by subtracting the two queries

1. Registered User
Join Date
Feb 2013
Posts
6

## Unanswered: Problem by subtracting the two queries

I have the following problem:
I have two tables. In the first (Baza) are:

ID
Mag_sifra
Int_Mag_Sifra
Naziv
Kolicina
Datum

In the second (Izdato) are:

ID
Mag_Sifra
Int_Mag_Sifra
Naziv
Izdato
Datum

Values Mag_Sifra, Naziv is the same in the both table. I made two queries. Query Baza and query Izdato. I want substract kolicina from first and Izdato from second. I get the following result:

14485 133 01 44 85 MANZETNA F-55/F 0 160 -160
14485 144 01 44 85 MANZETNA F-55/F 160 160 0

But i want this:

14485 133 01 44 85 MANZETNA F-55/F 0 0 0
14485 144 01 44 85 MANZETNA F-55/F 160 160 0

Where is 14485- Mag_Sifra, 133 01 44 85 and 144 01 44 85 - Int_Mag_Sifra, MANZETNA F-55/F - Naziv, first number is kolicina, second is izdato, and third is result.

Int_Mag_Šifra is the code of the place where the goods are in the warehouse. Its location. Mag_Šifra is the commodity code. Kolicina is the quantity of input goods, Izdato is the goods which have been issued.

I just have this two table and three query. Q3 = QueruBaza-CueryIzdato

2. Moderator
Join Date
Mar 2009
Posts
5,442
As far as I can see, The column Expr1 ([SumOfKolicina]-[SumOfIzdato] AS Expr1) provides the correct answer. What's wrong with it?
Code:
```SumOfKolicina	SumOfIzdato	Expr1
-------------------------------------------
3		2		    1,00
3		2		    1,00
42		2		   40,00
27		10		   17,00
3		2		    1,00
1		1		    0,00
0		1		   -1,00
42		2		   40,00
12		1		   11,00
10		3		    7,00
43		2		   41,00
14		12		    2,00
16		1		   15,00
3420		7		3.413,00
4		3		    1,00
8		2		    6,00
10		1		    9,00
1		1		    0,00
5		1		    4,00
13		13		    0,00
0		13		  -13,00
...```

3. Registered User
Join Date
Feb 2013
Posts
6
Yes, but i have subbstraction from Baza even i dont have input in Baza_izdavanje. Allways substract two times, for each Int_Mag_Sifra...
Last edited by Kolos; 02-10-13 at 08:57.

4. Moderator
Join Date
Mar 2009
Posts
5,442
You can try:
Code:
`IIf([SumOfKolicina] > 0, [SumOfKolicina]-[SumOfIzdato], 0) AS Expr1`

5. Registered User
Join Date
Feb 2013
Posts
6
It works. When it comes to the end result. But still i have Query with double entry of the same Naziv and Mag_Šifru, with different Int_Mag_Šifra. For the Mag_Sifra "14485" i have value 160 only for the Int_mag_Sifra "144 01 44 85" in the table. But, in the query i have that result for each "14485 code". For the 144 01 44 85 (thats true), 133 01 44 85 (i dont have input for the that in the table). Picture "2" is picture of table Izdato (same picture is for the query Izdato), picture "3" is picture of the Query 3, where you can see double entry.
Last edited by Kolos; 02-11-13 at 02:35.

6. Registered User
Join Date
Feb 2013
Posts
6
I solved the problem. The problem was formatting Int_Mag_Šifra field, which was formatted as text. In order not to re-enter all the data, I created two new query and use the Val function for the Int_Mag_Sifra field. Now it works perfectly. Thanks to everyone who tried to help me.

7. Moderator
Join Date
Mar 2009
Posts
5,442