How to sum the field based on every year!!! (1 Viewer)

[balaryan]

Hi All,

Good day. I am new born to VBA and tried writing a SQL query to produce the list of details in year wise DESCENDING order for the input - Agent ID.

In addition to that I need to get the sum of amount fields year wise in the report. How to achieve it???.

Please find the attached report for better clarification.

First three column in the attachment is the outcome of the below listed query. Whereas the fourth column is the expected result which needs to be included..

SELECT CessioneCredito.Data_Movimento, CessioneCredito.Anno,  CessioneCredito.Importo FROM CessioneCredito  
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Data_Movimento DESC;

How to alter the code in order to provide the SUM of Importo field every year.???

Thanks in advance. Appreciate your time.

 

[namliam]

Is this what you are looking for?

SELECT CessioneCredito.Anno,  sum(CessioneCredito.Importo) FROM CessioneCredito  
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
Group by CessioneCredito.Anno
order by CessioneCredito.Anno;

 

[balaryan]

Hi Namliam,

I need a report with four columns as per the attachment,

like

Data_Movimento Anno Sum Total

as shown in the attachment.

But the total should be displayed to minimum of Data_movimento field for every year.

So any idea how to achieve it in SQL query?

 

[namliam]

Perhaps something along these lines?

SELECT CessioneCredito.Data_Movimento, CessioneCredito.Anno,  CessioneCredito.Importo, null as total 
FROM CessioneCredito  
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
Union
SELECT null                          , CessioneCredito.Anno,  null                   , sum(CessioneCredito.Importo) 
FROM CessioneCredito  
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
Group by CessioneCredito.Anno
order by CessioneCredito.Anno;

Otherwize try a report?