problem by subtracting the two queries

[Koloss]

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

Wher 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.

I'm sorry cos my bad english and i houp you understand me.

[June7]

Can't you just build one query that joins the two tables linking on the two key fields?

[Koloss]

I try, but i get same result.

[June7]

I can see now that should probably do two aggregate queries and then join those queries. The issue seems to be the Int_Mag_Sifra field. What is relationship of these tables? Why is Int_Mag_Sifra in both tables but it is not always the same value in both? Should this field be exluded when grouping data?

[Koloss]

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.

[June7]

Do you care about location for this output? Sounds like it should be excluded when grouping records to sum Kolicina and Izdata. Don't include Int_Mag_Šifra in the queries and see what happens.

[Koloss]

It's the same result...

[Koloss]

Maybe i try create a second table with completely different names. And then to link it to the first.

[June7]

Sorry, really don't know your data structure well enough. Would have to work with the database.

[Koloss]

Maybe this examle can help, cos i dont see solution.

[Koloss]

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.