Report with several features !!W%^$%&^^ (1 Viewer)

balaryan · Jul 1, 2015

Hi All,

I am new to this forum and new to VBA n SQL skill set. Need your advice and direct me to the right path if i am wrong.

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 Designation DOJ SALARY TOTAL

2015 TEST1 MANAGER 24/05/2015 $12,000.00
TEST2 VP 12/05/2015 $15,000.00
TEST3 VC 01/04/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 ?

I have no clue whether its doable in a single query to find the required values, max and min of DOJ, and write logic to populate the total salary for mininum DOJ?

Else is it possible to make it in recordset by writing couple of queries and write logic to achieve it?

Its for REPORT.. not FORM.. kindly advice.

plog · Jul 1, 2015

You will need to use your query as the datasource for another query. You didn't provide your query's name, let's call your query "YourQuery". Also, you've chosen some poor field names. 'Year' and 'Name' are reserved words in Access and make coding a little more difficult. I suggest you rename them by prefixing them with what Date/Name they represent (e.g. BusinessYear, EmployeeName, etc.)

What you need to do in a new query is order your data within their Year groups and cacluate each Groups total. That way you will now which is the first record and which is the last, so that in the report you can control your Year and Totals values.

Use this SQL for that query:

Code:

SELECT YourQuery.Year, YourQuery.Name, YourQuery.Designation, YourQuery.DOJ, YourQuery.SALARY, DCount("[Year]","YourQuery","[Year]=" & [Year]) AS GroupSize, 1+DCount("[Year]","YourQuery","[Year]=" & [Year] & " AND [DOJ]>#" & [DOJ] & "#") AS GroupPos, DSum("[SALARY]","YourQuery","[Year]=" & [Year]) AS GroupTotal
FROM YourQuery
ORDER BY YourQuery.Year DESC , YourQuery.DOJ DESC;

Then you create your report based on that new query. Bring in all your fields like you want. When you run it, each record will have a Year and GroupTotals value. To put them on just the records you've described do this:

Change the Year Control Source to this:

=IIf([GroupPos]=1,[Year])

Change the GroupTotal Control Source to this:

=IIf([GroupPos]=[GroupSize],[GroupTotal])

Run that and it will produce the data you want in your aforementioned format.

There will be an issue if multiple people have the same Year and DOJ value and they are either the first/last in their group. They will each show the Year (if first) and Totals (if last). A way to avoid this is to add another sort criteria.

balaryan · Jul 1, 2015

Hi Plog,

Thanks for your quick response and apologies for not providing sufficient information before.

In my case, i will be using two tables to get the required output fields.

So Cessionecredito is Table one and Agenzia is Table two. I agree with you on referring the field names.

Also your sample query is quite greek and latin to me as i am new to VBA. I do agree that its fair enough to ask for more information on this. But i have no choice. Kindly bare with me and appreciate your patience.

Code:

SELECT YourQuery.Year, YourQuery.Name, YourQuery.Designation, YourQuery.DOJ, YourQuery.SALARY, DCount("[Year]","YourQuery","[Year]=" & [Year]) AS GroupSize, 1+DCount("[Year]","YourQuery","[Year]=" & [Year] & " AND [DOJ]>#" & [DOJ] & "#") AS GroupPos, DSum("[SALARY]","YourQuery","[Year]=" & [Year]) AS GroupTotal
FROM YourQuery
ORDER BY YourQuery.Year DESC , YourQuery.DOJ DESC;

From the above code, I understand DCOUNT (count the year and you display year for count '1' - first record)and DSUM will sum the total salary for every year.
What is the usage of the below listed piece of code

Code:

1+DCount("[Year]","YourQuery","[Year]=" & [Year] & " AND 
[DOJ]>#" & [DOJ] & "#") AS GroupPos

I just wrote my SQL code based on your inputs in my editor..

Code:

Private Sub Report_Open(Cancel As Integer)
 
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito,  CessioneCredito.Importo, " & _
                 " Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize, " & _
                 " 1 + DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno] & " AND [Data_Movimento] > #" & [Data_Movimento] & "#") AS GroupPos, " & _
                 " DSum("[Importo]","CessioneCredito","[Anno]=" & [Anno]) AS GroupTotal " & _
                 " 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; "

While saving it, it shows the code in RED COLOR which shows something is wrong.. I have no idea.. Any thought on this.

As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query ???

Change the Year Control Source to this:

=IIf([GroupPos]=1,[Year])

Change the GroupTotal Control Source to this:

=IIf([GroupPos]=[GroupSize],[GroupTotal])

or

Is it advisable to use recordset like writing in two different queries.

Code:

Dim ds1  As Recordset
Dim ds2  As Recordset
myquery1 = "select * from cessionecredito where....."
myquery2 = "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"

Set db = CurrentDb()
Set ds1 = db.OpenRecordset(myquery1)
Do Until ds.EOF

where the validation parts will be coded after this..

Sorry again for long post.

plog · Jul 1, 2015

1.
Your report groups its records by the YEAR field and it sorts them by DOJ. So I did that as well in my query. This code:

Code:

1+DCount("[Year]","YourQuery","[Year]=" & [Year] & " AND 
[DOJ]>#" & [DOJ] & "#") AS GroupPos

Tells you the position of that record within its group (1st, 2nd, 3rd, etc). That way you can make the YEAR field show for the first record and the Totals field show for the last record of a group.

-
2.

Why are you working in VBA? That wasn't part of the initial issue you posted. Further, your final result will be a Report Object, so I don't know how VBA figures into this.

-

3.

You said you had a query that gave you the results you wanted. My query builds upon that existing query you have. You should create a new query, bring in the query you referenced in your initial post and then follow my previous post's instructions.

balaryan · Jul 1, 2015

Plog,

As I am new to this forum as well to VBA / SQL query skills, no idea on that.

We write the SQL queries and design the report using VBA only. We use MS-ACCESS tables as database to fetch data.

Ultimately, Output of the query should be thrown or mapped to the report.

For your response "You said you had a query that gave you the results you wanted", I really dont have a single query to achieve the result i am looking for..

I assumed the below listed SQL query will give the expected results..

Code:

Private Sub Report_Open(Cancel As Integer)
 
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito,  CessioneCredito.Importo, " & _
                 " Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize, " & _
                 " 1 + DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno] & " AND [Data_Movimento] > #" & [Data_Movimento] & "#") AS GroupPos, " & _
                 " DSum("[Importo]","CessioneCredito","[Anno]=" & [Anno]) AS GroupTotal " & _
                 " 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; "

So any thought on the aforementioned code and where I should place the below listed code in the query ???

Change the Year Control Source to this:

=IIf([GroupPos]=1,[Year])

Change the GroupTotal Control Source to this:

=IIf([GroupPos]=[GroupSize],[GroupTotal])

I am in the middle of nowhere. please assist me ..

plog · Jul 1, 2015

I have no idea what your output is to be. I made a very educated guess at the beginning of this that it was to be an Access Report Object. I don't know if that's the case any more.

Within Access, you do not need to use VBA to solve this. You need to create a Query Object and a Report Object. Perhaps this link will help:

https://support.office.com/en-us/article/Create-a-simple-report-408e92a8-11a4-418d-a378-7f1d99c25304

Again, I have no idea what your final output is to be. My solution will work for an Access Report Object.

balaryan · Jul 2, 2015

Hi Plog,

That also will help in my case. I can create a object query and include that name in the recordsource of the REPORT.

But my doubt is how to achieve it in object query as i am aware it hold only one single query which should select the required fields and do the validation, sum the amount field and display the year and amount for first (Max) and last (Min) record in the report.

The link you provided given the information about the report. But you can provide me with the appropriate information as you understood my problem and the requirement.

Thanks again plog.

plog · Jul 2, 2015

I did. I really don't know where you are at in your query. You initially said you had a query that produced the values you wanted:

I write a simple SQL query and produce the values in the report by mapping the corresponding fields.

Now you say you don't have that query? I really don't know what point we are at in the process.

balaryan · Jul 2, 2015

Hi Plog,

Just to reiterate on my statement that i wrote two queries which listed below

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

Code:

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.

Code:

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;

As you aware of my requirement, I attached the snapshot of expected report in this post.

As i am novice to VBA and SQL query, I have no clue how to combine those two queries in order to produce my output report.

So you provided the query which listed below.

Code:

SELECT YourQuery.Year, YourQuery.Name, YourQuery.Designation, YourQuery.DOJ, YourQuery.SALARY, DCount("[Year]","YourQuery","[Year]=" & [Year]) AS GroupSize, 1+DCount("[Year]","YourQuery","[Year]=" & [Year] & " AND [DOJ]>#" & [DOJ] & "#") AS GroupPos, DSum("[SALARY]","YourQuery","[Year]=" & [Year]) AS GroupTotal
FROM YourQuery
ORDER BY YourQuery.Year DESC , YourQuery.DOJ DESC;

But when i tried using it, i am getting compiler error. Can you aid me again ?

balaryan · Jul 3, 2015

Hi Plog,

I need your help for the requirement listed below.

Code:

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

The aforementioned query will display the list of values (4 columns) from the table "CessioneCredito"

But i need to display the year for only first row (which hold Maximum value of Date field) for each and every year. How to achieve it. Kindly help me plog..

Output of the query is

Code:

Year    AgentName     Date                   Amount      Grand Total
2014   ABC               19/07/2015           10,000
       OSOF              10/04/2015           14,000         24,000

2013   SLKF              11/10/2014           13,000          13,000

2012   KOLF              11/10/2013           13,000          
       JJSKD             08/09/2013           12,000  
       SAFD              18/01/2013           12,000          37,000

Kindly help me in this.

plog · Jul 3, 2015

First, that is not the output of your query. That is your desired results.

Second, I did. Its all there in my 1st post.

Report with several features !!W%^$%&^^ (1 Viewer)

balaryan

Registered User.

Attachments

plog

Banishment Pending

balaryan

Registered User.

plog

Banishment Pending

balaryan

Registered User.

plog

Banishment Pending

balaryan

Registered User.

plog

Banishment Pending

balaryan

Registered User.

Attachments

balaryan

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread