Click here to Skip to main content
14,486,704 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 22-Mar-20 2:43am
v2
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
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.
Rate this:
Please Sign up or sign in to vote.

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:
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:
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
   
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 5 days ago
   
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 5 days ago
   
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 5 days ago
   
  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 :(((

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100