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

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
 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
 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
 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
more ▼

asked Mar 10 at 12:57 AM in Default

avatar image

61 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are 2 possible answers to this question. One is to send this query to the Crystal report and there make a field that will count the running sum. The other was is to create a query in Access that calculates the unions. After that create a query that calls that created query and you can join them.

more ▼

answered Mar 12 at 05:07 PM

avatar image

61 4

@tricman10 I am not sure whether an extra step to create a query def is needed or not. I remember that back in the day it was necessary to sometimes create and save a query def from select so it could be then used in the further query (like a join of it with itself), and after that delete the query def. Maybe it is still necessary to have this 3-step way, I don't know, but I believe that Access, though still pretty capricious, is somewhat better now, so it allows the joins of the sub-select aliases (might require an extra select all around the sub-select though).

Since you have already answered your own question, could you please clarify whether you still need help with this or not? Thank you.

Mar 12 at 07:09 PM Oleg

I have solved it. But thank you for you time and help! :)

Mar 13 at 11:41 AM tricman10
(comments are locked)
10|1200 characters needed characters left

Why not use the partition over and make this a stored procedure, call it from vba script in access and pass in any parameters?

more ▼

answered Mar 12 at 11:14 AM

avatar image


as far as I know, partition over does not exist in Access, only in SQL Server

Mar 12 at 11:53 AM tricman10

@tricman10 It looks like every select uses the same where clause, filtering the data for the same user, building, month and year. This means that the outer select will have the records only for one specific user for all returned rows. Please let me know if I understand the query correctly. The reason I am asking is because it is not clear why the order by user is there if all rows are for the same user, and whether your running sum needs to be for all rows or for some group of rows in the result (partitioned).

Unfortunately, the ways to get the running total in Access is either via DSUM or via triangular join of the table with itself, both ways are expensive and slow, but you might get away if the number of returned rows is not huge. It would really help if you could post some dummy sample data or else clarify about whether the running total needs to be for all rows or for groups within the results. Thank you.

Mar 12 at 01:11 PM Oleg

@tricman10, sorry I made some assumptions before asking the proper questions. Is this an Access forms app with linked tables to SQL Server or is the data all stored in Access as well? If the data is stored in SQL and you are querying from Access, that is where I was saying you might be better off creating a stored proc in SQL and then just use ADO in vba script to execute the stored proc and get the data you need. Otherwise, if the data is only stored in Access, Oleg has a good answer.

Mar 12 at 01:30 PM Ryancerney

it is a c# app that uses access as a database

Mar 12 at 02:40 PM tricman10

@tricman10 Do you always filter for the single user, month and year? It looks like if you run the query as written in your question, you should get the results for the single user (so same last name in all rows if multiple rows are returned), and the only way to get multiple rows when the data is filtered as it is, is if there are rows for different days of the same month. Is this the case? Please clarify so I can try to mock up some data on my end. Thank you.

Mar 12 at 04:13 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 10 at 12:57 AM

Seen: 40 times

Last Updated: Mar 13 at 11:41 AM

Copyright 2018 Redgate Software. Privacy Policy