How to use alias of a subquery to get the running total?

[tricman10]

I am using Access database and C#.
I have a union of 3 tables for calculating some balance and I need to get the running sum of that balance, but I can't use partition over, because I must do it with the sql query that is gonna work in Access. My problem is that I cannot use join on a alias subquery, it won't work.

How can I use alias in a join to get the running total? Or any other way to get the sum that is not with partition over, because it does not exist in Access. This is my code so far:

SELECT korisnik_id,imePrezime,datum,Dug,Pot,(Dug-Pot) AS Balance
 FROM(
 SELECT korisnik_id, k.imePrezime, r.datum,SUM(IIF(u.jedinstven = 1, r.cena, k.kvadratura * r.cena)) AS Dug,'0' AS Pot
 FROM Racun r
 INNER JOIN Usluge u on r.usluga_id = u.ID
 INNER JOIN Korisnik k on r.korisnik_id = k.ID
 WHERE korisnik_id =1 AND r.zgrada_id = 1 and r.mesec = 1 and r.godina = 2017
 group by korisnik_id,k.imePrezime,r.datum
 UNION ALL
 SELECT korisnik_id, k.imePrezime, rp.datum, SUM(IIF(u.jedinstven = 1, rp.cena, k.kvadratura * rp.cena)) AS Dug,'0' AS Pot
 FROM RacunP rp
 INNER JOIN Usluge u on rp.usluga_id = u.ID
 INNER JOIN Korisnik k on rp.korisnik_id = k.ID
 WHERE korisnik_id =1 AND rp.zgrada_id = 1 and rp.mesec = 1 and rp.godina = 2017
 group by korisnik_id,k.imePrezime,rp.datum
 UNION ALL
 SELECT uu.korisnik_id, k.imePrezime, uu.datum,'0' AS Dug, SUM(uu.iznos) AS Pot
 FROM UnosUplata uu
 INNER JOIN Korisnik k on uu.korisnik_id = k.ID
 WHERE korisnik_id =1
 group by uu.korisnik_id, k.imePrezime, uu.datum
 ) AS a     
 ORDER BY korisnik_id

 

[plog]

Where exactly are we working? In Access or SQL Server? I really think if you divide and conquer this thing, no need for aliases. I recommend break that query into a bunch of sub queries that exist as their own Acccess query objects or SQL Server Views.

First I would create a simple UNION of your 3 data sources--no INNER JOINS, no criteria:

SELECT {Fields} FROM Racun
UNION ALL
SELECT {Fields} FROM RacunP
UNION ALL 
SELECT {Fields} FROM UnosUplata

Name that Sub1. Then use it in a query, bring in your INNER JOIN tables there and apply your criteria in that query. Save it as Sub2.

Then, you can create your actual query with your Running sum by either using a correlated sub-query (in Access or SQL Server) or DSUM (Access).

I just think you are trying to do too much in that one set of code. Divide and conquer, get the pieces leading up to it working and you can much easier see when/where it falls apart.

 

[tricman10]

Thank you, that was the way to do it!