Count distinct lines in a report

[Dominic]

Hi all

I have a simple Access report that lists work order numbers, along with
various information against this order number. An individual order number
may appear more than once in this report.

I would like at thge bopttom of the report, the number of different work
order numbers in the report. I am trying to build an expression / formula to
do this, but am struggling a bit.

Thanks for any help anyone can throw my way.

DominicB

 

[John Spencer]

Are the work order numbers all printing together in the report in one location
or are they scattered around the report?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dominic]

Hi John

Thanks for taking the time to answer.

I have a simple database table recording production - a list of work orders
(that may appear more than once) and against each order the machine ID, run
speed, design etc.

There is then a query against this table where I set up the criteria I want
to apply to the table.

The report is based on that query, and is a really simple report listing
work order, run speed, design etc.

I would like to count how many work orders there are, and if the work order
appears more than once, don't count it a second time.

The workorders aren't scattered about the report - they just appear in one
column, but may appear multiple times (for example if the job is printed in
batches on two seperate days on two different machines).

I used to have this information in Excel and had a formula to do this, but
have moved to Access as it's better suited for the job, but cannot figure
this problem out.

Thanks for reading

Dominic

 

[John Spencer]

I realize they appear in one horizontal location. I was trying to determine
if they were like the following in vertical location (all numbers clustered
together.
Workorder 1
Workorder 4
Workorder 4
WorkOrder 4
Workorder 2
Workorder 2
Workorder 3


Or like the following where the workorders are not clustered together.
Perhaps in date order of job being performed.
Workorder 1
Workorder 2
Workorder 4
Workorder 4
Workorder 2
Workorder 4
WorkOrder 3

With the first you can get a count using the report and a running sum based on
a grouping by workorder. Either way you can use a custom function to get the
count (see following)

Check out Allen Browne's article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

 

[Dominic]

Hi John

I see what you mean now about the work order numbers - the second example is
more how my data is arranged, for no particular reason, just that the
original imported data is sorted by machine number.

I will check out Allen Browne's site now. Many thanks for the pointers.

Dominic