Count in Report

[majid.pervaiz]

Dear Friends,

First of all thank you for your usual help on this forum.

I have a question on report today, I have a field in table SLA_Met (text data type).
Each staff may meet SLA or Not for a particular task, therefore I need to show something like this but don't know how to do that


Grouped by Task Type - XYZ Task
Staff name
Mark
SLA Met 5
SLA Not Met 6
Michael
SLA Met 2
SLA Not Met 1
Arnold
SLA Met 3
SLA Not Met 4

as of now I have tried to use =Count(*) but it is showing all the records which query is returning.

 

[arnelgp]

put two unbound textbox on the detail section of your report.
one for sla_met and the other sla_not_met.
arrange them as you want to to appear on the report.
on the controlsource property of sla_met, put:

=nz(dcount("*", "yourTableName", "[staff name] = '" & [staff name] & "' and [sla_met] like 'sla met*'"), 0)

on controlsource of sla_not_met:

=nz(dcount("*", "yourTableName", "[staff name] = '" & [staff name] & "' and [sla_met] like 'sla not met*'"), 0)

colored blue text should be replaced with the correct control name in your report.

 

[majid.pervaiz]

Dear I want all the staff name to appear in this report.. so for [staff name], what I should put here.
I am so sorry I am bit new that's why bother a lot.

 

[arnelgp]

create a an Aggregate query against your table:

SELECT [staff name] from table GROUP BY [staff name] ORDER BY 1;

create a report based on this query.
now insert to unbound textbox control on the detail section and follow my first post.

 

[majid.pervaiz]

Dear Arnelgp,

I have attached the sample database for your support.

I need a report based on query "Individual_Completed_Task_Report"

the report is first group by Task_Type then Allocated to, under each staff name I want to see how many tasks SLA were met and how many were not.

I would be grateful to you if you can support to achieve this.

 

[arnelgp]

i modified this Individual_Completed_Task_Report query (see changes).
do you want to based your report on this query.
just add the two new calculated field from the query to your report.

 

[majid.pervaiz]

Not mandatory to use query if report meets my requirement .. I will be more than happy. Actually left my work and couldn't open database on Macbook... Will check tomorrow morningI am thankful for your help

 

[majid.pervaiz]

Dear Arnelgp,

I am really grateful to you for your excellent support, wish I can offer you a dinner as a thanks.

Now, based on amendments in the query the report is showing correctly (see the attached report screenshot).

Many thanks once again Brother, I may bother you again if need for something else.

 

[arnelgp]

nice report!
may i suggest something.
remove the Alternate Back Color on Group Section and Detail Section.
to highlight the Group section just make the Back Color to something darker (ie, blue).