SUMM value of notes

[sal21]

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

[HansV]

Does this do what you want?

TRANSFORM Sum(NR) AS SummaNOTES SELECT AGENCY, DATE FROM MyTable GROUP BY AGENCY, DATE PIVOT TRANSACTION

[sal21]

Does this do what you want?

TRANSFORM Sum(NR) AS SummaNOTES SELECT AGENCY, DATE FROM MyTable GROUP BY AGENCY, DATE PIVOT TRANSACTION

For test for UIC 001:

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

[HansV]

Try this:

SELECT DATA_OP, AGENCY, UIC, Sum(IIf([TRANSACTION]='VB',[NR],-[NR])) AS Summa FROM MYTABLE GROUP BY DATA_OP, AGENCY, UIC