Use sum and partıtıon in acces query

Question

0.00/5 (No votes)

See more:

hi,
İ cant use theese codes in may access query
i can use it oracle query but it doesnt work in access query

SUM(SalePrice) OVER(PARTITION BY OrderCategory order BY orderDate)

i have a table lıke this and i want last column sum(saleprice)lıke ,
OrderCategory orderDate SalePrice sum(saleprice)
xxxx 01.01.2020 50 50
xxxx 10.02.2020 150 200
xxxx 01.03.2020 175 375

What I have tried:

<pre>SUM(SalePrice) OVER(PARTITION BY OrderCategory order BY orderDate)

Posted 21-Mar-20 21:26pm

Member 14588284

Updated 22-Mar-20 2:43am

v2

Add a Solution

Comments

Richard MacCutchan 22-Mar-20 4:13am

What do you mean by "İ cant use theese codes in may access query"?

OriginalGriff 22-Mar-20 4:19am

"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.
Use the "Improve question" widget to edit your question and provide better information.

Member 14588284 22-Mar-20 4:36am

https://yadi.sk/d/JrEWUe9Jd8U-dQ
error lıke this

OriginalGriff 22-Mar-20 4:49am

I'm not going near a random website to find out what you posted there.
You want to show us an error? Copy'n'paste, then post the text here.

Member 14588284 22-Mar-20 5:13am

run-time error '-2147217900 (80040e14)';

OriginalGriff 22-Mar-20 5:27am

OK. And? What does Google say about that number? Did you check?

Member 14588284 22-Mar-20 5:38am

of course i did
i dont find ayntihng then i write here

OriginalGriff 22-Mar-20 5:42am

Oh. That surprises me, because for me, Google returne 3,550 hits in 0.36 seconds:

https://www.google.com/search?q=run-time+error+%27-2147217900+(80040e14)%27&oq=run-time+error+%27-2147217900+(80040e14)%27&aqs=chrome..69i57.141424j0j7&sourceid=chrome&ie=UTF-8

Most of which would appear to contain solutions ...

Member 14588284 22-Mar-20 5:48am

i look them but nothing an answer for my question
did u read my question?

simply i say ,
i want like current account statement

Member 14588284 22-Mar-20 4:32am

OrderCategory orderDate SalePrice sum(saleprice)
xxxx 01.01.2020 50 50
xxxx 10.02.2020 150 200
xxxx 01.03.2020 175 375

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2020-03-21T23:23:00

Solution 1

EQUIVALENT SQL PARTITION BY CLAUSE IN ACCESS? - Microsoft Community[^].

Posted 21-Mar-20 23:23pm

Richard MacCutchan

Comments

Member 14588284 22-Mar-20 6:03am

i read but there for all rows only sum of row
but i want like this
A B c sum(c)
xxxx 01.01.2020 50 50
xxxx 10.02.2020 150 200
xxxx 01.03.2020 175 375

Member 14588284 22-Mar-20 6:12am

but that gives me an idea and i find my solution
thanks a lot

Member 14588284 22-Mar-20 6:34am

But it gives me wrong total when more then one ıd in same date

Richard MacCutchan 22-Mar-20 6:40am

What is "it"?

Member 14588284 22-Mar-20 6:43am

SORGU = "SELECT CARKOD,TARIH,KAYITNO," & BORC & " AS BORC," & ALACAK & " AS ALACAK," & _
" (SELECT SUM(" & BORC & "-" & ALACAK & ") FROM CARTH001 WHERE TARIH<=A.TARIH AND CARKOD=A.CARKOD )AS BAKIYE " & _
" FROM CARTH001 A GROUP BY " & ALACAK & "," & BORC & ",CARKOD,TARIH,KAYITNO ORDER BY CARKOD,TARIH,KAYITNO"

Richard MacCutchan 22-Mar-20 6:51am

You need to break that statement into smaller parts and see what each one returns. It is impossible for anyone here to test it.

Member 14588284 22-Mar-20 6:57am

https://yadi.sk/d/4q_RDVxb2JEqtg

in here excample work

Maciej Los 22-Mar-20 7:54am

5ed!

Member 14588284 22-Mar-20 8:08am

?

Maciej Los 22-Mar-20 8:47am

Please, red this: Code Project Rating and Reputation FAQ[^]

I voted 5 for Richard's answer due to valuable content ;)

BTW: Please, see my answer.

Maciej Los · Answer 2 · 2020-03-22T02:44:00

Solution 2

Well, you need to change only one value to get correct result:

CARKOD		ISLTARIH			TARIH		KAYITNO	BORC	ALACAC	BAKYIE
12001001	2020-03-21 15:41:19	2020-03-01	3		500				500
12001001	2020-03-21 16:09:16	2020-03-05	4		600				1700
12001001	2020-03-21 16:09:16	2020-03-03	8		600				1700
12001001	2020-03-21 22:20:51	2020-03-20	5				-500	1200
12001001	2020-03-21 23:03:44	2020-03-30	6				-500	700
12001001	2020-03-21 23:03:45	2020-03-30	7				-500	200

In your original file underscored value is: 2020-03-21 23:03:44, so SUM(TUTAR) for the financial operation made in the same second is 1000! That's why you've got the value of 200 instead of 700.

Here is a query used by me:

SQL

SELECT src.CARKOD, src.ISLTARIH, src.TARIH, src.KAYITNO, IIF(src.BA='B', src.TUTAR, NULL) AS BORC, IIF(src.BA='A', src.TUTAR*-1, NULL) AS ALACAC, (SELECT SUM(IIF(ps.BA='B', ps.TUTAR, ps.TUTAR *-1)) 
    FROM CARTH001 AS ps
    WHERE ps.ISLTARIH <= src.ISLTARIH) AS BAKYIE
FROM CARTH001 AS src
ORDER BY src.ISLTARIH, src.KAYITNO;

[EDIT]
Accordingly to the comments to this answer...

A query should looks like:

SQL

SELECT src.CARKOD, src.ISLTARIH, src.TARIH, src.KAYITNO, IIF(src.BA='B', src.TUTAR, NULL) AS BORC, IIF(src.BA='A', src.TUTAR*-1, NULL) AS ALACAC
FROM CARTH001 AS src
ORDER BY src.TARIH, src.ISLTARIH;

The rest have to be done in MS Excel, because MS Access does not support ROW_NUMBER(PARTITION BY ... ORDER BY ...). Although it is available to achieve by several subqueries, but final solution is - at least - ugly...

I've created MS Excel file, which gets data from Access database and "calculate" pending sum based on it.
Excel - pending sum on Access data[^]
File is available to download between 07:00AM and 10:59.59PM UTC+1 (Warsaw)

Result:

CARKOD 	ISLTARIH	 TARIH 	 KAYITNO 	 BORC 	ALACAC	BAKYIE
 12001001 	2020-03-11	2020-03-01	 3,00    	 500,00    		500
 12001002 	2020-03-15	2020-03-01	 1,00    		-500	0
 12001001 	2020-03-10	2020-03-03	 8,00    	 600,00    		600
 12001001 	2020-03-12	2020-03-05	 4,00    	 600,00    		1200
 12001001 	2020-03-22	2020-03-05	 13,00    		-400	800
 12001001 	2020-03-20	2020-03-15	 12,00    	 300,00    		1100
 12001001 	2020-03-13	2020-03-20	 5,00    		-500	600
 12001001 	2020-03-18	2020-03-20	 10,00    	 750,00    		1350
 12001001 	2020-03-19	2020-03-20	 11,00    	 30,00    		1380
 12001001 	2020-03-14	2020-03-30	 7,00    		-500	880
 12001001 	2020-03-16	2020-03-30	 9,00    		-500	380
 12001001 	2020-03-17	2020-03-30	 6,00    		-500	-120

Posted 22-Mar-20 2:44am

Maciej Los

Updated 22-Mar-20 10:43am

v2

Comments

Member 14588284 22-Mar-20 9:39am

Thank u very much Maciej Los
i changed theese codes and i did my want
again thank you very much :)

Maciej Los 22-Mar-20 9:44am

You're very welcome.

Richard MacCutchan 22-Mar-20 10:08am

I think your 5 from me is much more well deserved.

Maciej Los 22-Mar-20 10:09am

Thank you, Richard.
:)

Member 14588284 22-Mar-20 10:40am

Hi again
can you look again codes ?
i changed order by date and totals are not true

https://yadi.sk/d/4q_RDVxb2JEqtg

Maciej Los 22-Mar-20 12:16pm

You need to define what's wrong with query changed by you...
Why did you change order by clause?
I have no idea what you want to achieve...

Member 14588284 22-Mar-20 12:58pm

because i must order by date (TARIH)

Maciej Los 22-Mar-20 13:38pm

And the expected output is...

Member 14588284 22-Mar-20 14:08pm

SO?

Maciej Los 22-Mar-20 14:11pm

I was asking you about example output...

Maciej Los 22-Mar-20 14:11pm

Check this out:
Hide Copy Code

SELECT src.CARKOD, src.ISLTARIH, src.TARIH, src.KAYITNO, IIF(src.BA='B', src.TUTAR, NULL) AS BORC, IIF(src.BA='A', src.TUTAR*-1, NULL) AS ALACAC, (    SELECT SUM(IIF(ps.BA='B', ps.TUTAR, ps.TUTAR *-1))     FROM CARTH001 AS ps    WHERE DCount('TARIH', 'CARTH001', 'TARIH<=#' & ps.TARIH & '# AND ISLTARIH<=#' & ps.ISLTARIH & '#') <= DCount('TARIH', 'CARTH001', 'TARIH<=#' & src.TARIH & '# AND ISLTARIH<=#' & src.ISLTARIH & '#') ) AS BAKYIEFROM CARTH001 AS srcORDER BY src.TARIH, src.ISLTARIH;

And the result is:

CARKOD	ISLTARIH	TARIH	KAYITNO	BORC	ALACAC	BAKYIE
12001001	2020-03-11 15:41:19	2020-03-01	3	500		1100
12001002	2020-03-15 23:03:41	2020-03-01	1		-500	600
12001001	2020-03-10 11:09:16	2020-03-03	8	600		1100
12001001	2020-03-12 16:09:16	2020-03-05	4	600		1200
12001001	2020-03-22 17:01:20	2020-03-05	13		-400	100
12001001	2020-03-20 16:44:01	2020-03-15	12	300		100
12001001	2020-03-13 22:20:51	2020-03-20	5		-500	700
12001001	2020-03-18 16:40:03	2020-03-20	10	750		850
12001001	2020-03-19 16:40:30	2020-03-20	11	30		380
12001001	2020-03-14 23:03:14	2020-03-30	7		-500	100
12001001	2020-03-16 23:03:42	2020-03-30	9		-500	380
12001001	2020-03-17 23:03:47	2020-03-30	6		-500	-120

Member 14588284 22-Mar-20 15:12pm

but results not true

CARKOD	ISLTARIH	TARIH	KAYITNO	BORC	ALACAC	BAKYIE
12001001	2020-03-11 15:41:19	2020-03-01	3	500		500
12001002	2020-03-15 23:03:41	2020-03-01	1		-500	0
12001001	2020-03-10 11:09:16	2020-03-03	8	600		600
12001001	2020-03-12 16:09:16	2020-03-05	4	600		1200
12001001	2020-03-22 17:01:20	2020-03-05	13		-400	800
12001001	2020-03-20 16:44:01	2020-03-15	12	300		1100
12001001	2020-03-13 22:20:51	2020-03-20	5		-500	400
12001001	2020-03-18 16:40:03	2020-03-20	10	750		1150
12001001	2020-03-19 16:40:30	2020-03-20	11	30		1180
12001001	2020-03-14 23:03:14	2020-03-30	7		-500	600
12001001	2020-03-16 23:03:42	2020-03-30	9		-500	100
12001001	2020-03-17 23:03:47	2020-03-30	6		-500	-600

Member 14588284 25-Mar-20 9:12am

Canu send me file within theese codes please
i didnt work codes in my file
eror:
https://yadi.sk/i/_yV7a1UNSBnDwg

Member 14588284 22-Mar-20 14:24pm

i copy codes my file but it didnt work for #

could you send me file please
thanks

Member 14588284 22-Mar-20 14:26pm

my mail adress is : [email adrress has been deleted due to spam robots]

Maciej Los 22-Mar-20 16:44pm

See updated answer.

Member 14588284 22-Mar-20 16:54pm

Thanks for your answer but i try this before
when "CARKOD" add anotherone then "BAKIYE" Not true

Maciej Los 23-Mar-20 3:36am

Download zipped file again.
You didn't specify that CARKOD is important. This is the last time when i'm helping you in this thread.
Good luck!

Member 14588284 25-Mar-20 8:57am

Thanks for all Maciaj Los.
But i have to do it in one query.
CARKOD,TARIH,BORC,ALACAK,BAKIYE

And i didnt any solution for this

Member 14588284 25-Mar-20 9:04am

  TOPLAM = "SELECT B.CARKOD,B.TARIH,B.ISLTARIH ,SUM(" & BORC & "- " & ALACAK & ") AS TOPL FROM CARTH001 B  GROUP BY B.CARKOD,B.TARIH,B.ISLTARIH ORDER BY B.CARKOD,B.TARIH,B.ISLTARIH ASC "
TOPLAM2 = "SELECT  CARKOD,TARIH,ISLTARIH,TOPL FROM (" & TOPLAM & ") "
TOPLAM3 = "SELECT SUM(TOPL) FROM (" & TOPLAM2 & ")  WHERE CARKOD=A.CARKOD AND TARIH<=A.TARIH AND ISLTARIH<=B.ISLTARIH "
  SORGU = "SELECT A.CARKOD,A.TARIH,A.ISLTARIH,IIF(A.BA='B',A.TUTAR,0)AS BORC ,IIF(A.BA='A',A.TUTAR*-1,0) AS ALACAK,(" & TOPLAM3 & ") AS BAKIYE FROM CARTH001 A  order by  A.CARKOD,A.TARIH,A.ISLTARIH"

I wrote a code like this, but I couldn't :(((