Results 1 to 7 of 7
  1. #1
    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
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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 
    ...
    Have a nice day!

  3. #3
    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...
    Attached Thumbnails Attached Thumbnails untitled4.JPG   untitled2.JPG  
    Last edited by Kolos; 02-10-13 at 08:57.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can try:
    Code:
    IIf([SumOfKolicina] > 0, [SumOfKolicina]-[SumOfIzdato], 0) AS Expr1
    Have a nice day!

  5. #5
    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.
    Attached Thumbnails Attached Thumbnails 2.JPG   3.JPG  
    Last edited by Kolos; 02-11-13 at 02:35.

  6. #6
    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. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome, and thanks for the info!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •