Need a SUM field with the required format.. !!!!

  • Hi All,

    Good day. I am new to this forum as well to SQL query skill. I am sending the required information about the requirement and attached the format of the new report.

    To get this, I should access two tables, one table named Agenzie which populates the field “Denominazione” and other table name is CessioneCredito which populates the fields Anno, Data_Movimento, Note_Liq_Cessione_Credito and Importo.

    I wrote a query which listed below and the output report. But its not displaying the field Data_Movimento in DESC order even though I had issued ORDER BY Data_Movimento field as DESCENDING.

    Query 1 : This query generate the list of records with fields Anno (year), Data_Movimento (Date), Note description and Agent description in descending order.

    SELECT CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione

    FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia

    WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))

    ORDER BY CessioneCredito.Data_Movimento DESC;

    Also I should follow the below conditions in my output report just to get like the attached report (check for reference)

    YEAR column: Display Value only once for the FIRST ROW for Maximum value of DOJ field value for each set of YEAR. If it has only one record, it display that year.

    I will be writing my SQL QUERY ORDERBY DATE_OF_JOINING (DOJ) in DESCENDING sequence. So it will display the records in aforementioned order. But i have no clue how to supress or reset the YEAR value for other records (Other than first row) for each set of year

    TOTAL Column: Display Total Value for SALARY field only once for the LAST ROW of each set of year record. I will use SUM function to add those salary fields to get total value. While displaying, how to make it available only for last record alone?

    Query 2: This query find the sum of amount field 'Importo', Max and min value of Data_Movimento for each and every year from the Cessionecredito table in descending order.

    SELECT Sum(CessioneCredito.Importo) AS SumOfImporto, CessioneCredito.Anno, Max(CessioneCredito.Data_Movimento) AS MaxOfData_Movimento, Min(CessioneCredito.Data_Movimento) AS MinOfData_Movimento

    FROM CessioneCredito GROUP BY CessioneCredito.Anno, CessioneCredito.ID_Agente

    HAVING (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))

    ORDER BY CessioneCredito.Anno DESC , Max(CessioneCredito.Data_Movimento) DESC;

    Using this Max date and Min date, I thought to move Anno (Year field) and Sum (Amount) field to the report.

    How to incorporate this change (query 2) in query 1 ??? if there is any other option, please let me know

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply