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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|