do not count duplicates fields in report (1 Viewer)

joeyd11ph

Registered User.
Local time
Today, 11:45
Joined
Jul 16, 2019
Messages
38
Hello

I need to count as one only any Duplicates fields in my report.

I managed to do it in Job No row using the below procedure but fail in both received and completed date.

"To count the number of distinct instances of a Job No in the period covered by the report include a hidden text box, txtIDCounter say, in the group header or footer of the Job No group, with a ControlSource property of =1, and a RunningSum property of Over All. Then in the report footer, not the group footer, I include a visible text box whose ControlSource property references the hidden text box with =[txtIDCounter ]."

Please, I appreciate very much any kind of help to meet my expected result of 2 and 3 as indicated in the attached pic.

Thanks in advance
Joey
 

Attachments

  • Duplicate value.png
    Duplicate value.png
    23.9 KB · Views: 87

dynamictiger

Registered User.
Local time
Today, 18:45
Joined
Feb 3, 2002
Messages
270
I think this would be achievable in queries.

A query giving you select distinct job numbers and count these records. This give you the job count.

Then a similar query with closed jobs only, again using a select distinct to get only the unique records.

These could be unioned with a bit of imagination to provide the information you are looking for. This may be able to be subreported to the main report or used as a data source for a recordset or...
 

plog

Banishment Pending
Local time
Today, 13:45
Joined
May 11, 2011
Messages
11,611
Agree with dynamictiger that this should be done via a queries, but I think 2 will suffice. The first query will assign a status to each Job Number and the second will aggregate them. Then, the bottom, colored portion of your posted image will become a subreport based on that aggregate query.

In the first query, what you would do is assign a status to each Job number, then use that query as the source for your subreport. The status field in that query would be a calculated field and look like this:

JobStatus: IIf(Isnull([DateCompleted]), IIf(Isnull([DateReceived], "Not Received", "Pending"), "Completed")

That field will determine the status of each Job and will allow you to run a subsequent aggregate query to total them.
 

joeyd11ph

Registered User.
Local time
Today, 11:45
Joined
Jul 16, 2019
Messages
38
Thank you very much plog and dynamictiger. It works as expected.....

Cheers guys......

joey
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:45
Joined
May 7, 2009
Messages
19,169
there is also a vba alternative.
 

Attachments

  • job.zip
    35.9 KB · Views: 72

joeyd11ph

Registered User.
Local time
Today, 11:45
Joined
Jul 16, 2019
Messages
38
Hi Arnelgp,

I will keep your file for my future ref.

Thanks for your time and assistance

Joey
 

Users who are viewing this thread

Top Bottom