With sql....
i have a table with fields:
AGENCY (Code of agency)
DATE (Date of transaction)
UIC (code name of currency)
TRANSTACTION (i can have AB when purchase notes VB when i sell)
NR (number of notes)
I need to calculate for each dates the algebric summ from AB and VB based AGENCY
note:
all fields can have duplicates, in this case i think a group by... is need
SUMM value of notes
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMM value of notes
Does this do what you want?
TRANSFORM Sum(NR) AS SummaNOTES SELECT AGENCY, DATE FROM MyTable GROUP BY AGENCY, DATE PIVOT TRANSACTION
TRANSFORM Sum(NR) AS SummaNOTES SELECT AGENCY, DATE FROM MyTable GROUP BY AGENCY, DATE PIVOT TRANSACTION
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: SUMM value of notes
For test for UIC 001:HansV wrote:Does this do what you want?
TRANSFORM Sum(NR) AS SummaNOTES SELECT AGENCY, DATE FROM MyTable GROUP BY AGENCY, DATE PIVOT TRANSACTION
DATA_OP AGENCY TRANSACTION UIC NR
08/10/2014 0500 VB 001 2500
08/10/2014 0500 AB 001 550
08/10/2014 0500 AB 001 583
i need:
DATA_OP AGENCY UIC NR
08/10/2014 0500 001 -1367
Last edited by sal21 on 12 Oct 2014, 09:58, edited 1 time in total.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SUMM value of notes
Try this:
SELECT DATA_OP, AGENCY, UIC, Sum(IIf([TRANSACTION]='VB',[NR],-[NR])) AS Summa FROM MYTABLE GROUP BY DATA_OP, AGENCY, UIC
SELECT DATA_OP, AGENCY, UIC, Sum(IIf([TRANSACTION]='VB',[NR],-[NR])) AS Summa FROM MYTABLE GROUP BY DATA_OP, AGENCY, UIC
Best wishes,
Hans
Hans