Report with several queries

[balaryan]

As a part of my case study, I got a scenario to produce the Employee report for the list of years.

It has following fields named Year, Name, Designation, DOJ, Salary (from table EMPLOYEE) and Total. I write a simple SQL query and produce the values in the report by mapping the corresponding fields.

---------------------------------------------------------------
        ANNUAL FISCALE REPORT - EMPLOYEE HUB
---------------------------------------------------------------
 
Year    Name    Role     DOJ         SALARY        TOTAL
 
2015    TEST1     MANAGER    24/05/2015   $12,000.00    
     TEST2     VP    12/04/2015   $15,000.00    
     TEST3     VC    01/02/2015   $13,500.00       $40,500.00    
 
2014     TEST4     MANAGER    25/03/2014   $15,000.00       $15,000.00    
 
2013     TEST5     MANAGER    03/12/2013   $12,000.00     
     TEST6     VP    23/08/2013   $18,000.00    $20,000.00 
                           ----------
                                       $75,500.00

I have below listed conditions should be satisfied for the aforementioned requirement of report.

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 ?

[zmbd]

This appears at first glance to be a homework question and such threads without work shown are often closed or removed, even if answered by another member.

HOWEVER If you will show your work, often someone here will provide some hints... better to teach than to dismiss :)

One such hint, establish >> Database Normalization and proper Table Structures.
The SQL will almost write itself at that point (well... maybe not that easy... :) )

[balaryan]

Hi Friend,

Thanks for the instruction. As this is my first post, i will correct it from now on.

My Requirement is to generate the report using VBA - SQL query by accessing the MS Access tables. attached the format of new report.

I have no clue how to get the required output in a single query.

Whereas, i wrote two separate queries.

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;
 

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;

Can you guide me how to get the report in the attached format as i am using VBA and SQL query, reports to produce the output from the two different tables from MS ACCESS.

Kindly let me know if i missed any information.