By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,474 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

Report with several queries

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. ---------------------------------------------------------------
  2.         ANNUAL FISCALE REPORT - EMPLOYEE HUB
  3. ---------------------------------------------------------------
  4.  
  5. Year    Name    Role     DOJ         SALARY        TOTAL
  6.  
  7. 2015    TEST1     MANAGER    24/05/2015   $12,000.00    
  8.      TEST2     VP    12/04/2015   $15,000.00    
  9.      TEST3     VC    01/02/2015   $13,500.00       $40,500.00    
  10.  
  11. 2014     TEST4     MANAGER    25/03/2014   $15,000.00       $15,000.00    
  12.  
  13. 2013     TEST5     MANAGER    03/12/2013   $12,000.00     
  14.      TEST6     VP    23/08/2013   $18,000.00    $20,000.00 
  15.                            ----------
  16.                                        $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 ?


Jul 1 '15 #1
Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,397
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... :) )
Jul 2 '15 #2

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione
  2. FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
  3. WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
  4. ORDER BY CessioneCredito.Data_Movimento DESC;
  5.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(CessioneCredito.Importo) AS SumOfImporto, CessioneCredito.Anno, Max(CessioneCredito.Data_Movimento) AS MaxOfData_Movimento, Min(CessioneCredito.Data_Movimento) AS MinOfData_Movimento
  2. FROM CessioneCredito GROUP BY CessioneCredito.Anno, CessioneCredito.ID_Agente
  3. HAVING (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
  4. 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.
Attached Images
File Type: jpg Format of new report.jpg (14.9 KB, 190 views)
Jul 2 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.