A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count distinct lines in a report



 
 
Thread Tools Display Modes
  #1  
Old January 29th, 2010, 10:14 AM posted to microsoft.public.access.reports
Dominic
external usenet poster
 
Posts: 31
Default Count distinct lines in a report

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
  #2  
Old January 29th, 2010, 03:17 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Count distinct lines in a report

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 wrote:
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

  #3  
Old January 30th, 2010, 12:33 PM posted to microsoft.public.access.reports
Dominic
external usenet poster
 
Posts: 31
Default Count distinct lines in a report

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" wrote:

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 wrote:
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

.

  #4  
Old January 30th, 2010, 08:38 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Count distinct lines in a report

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 wrote:
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" wrote:

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 wrote:
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

.

  #5  
Old January 31st, 2010, 09:51 AM posted to microsoft.public.access.reports
Dominic
external usenet poster
 
Posts: 31
Default Count distinct lines in a report

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



"John Spencer" wrote:

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 wrote:
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" wrote:

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 wrote:
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
.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.