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
|
|||
|
|||
Counting Minus Duplicates
I have a report where I need to count the number of individuals confined by
ethnicity. In this report I can not count not individual who confined more than once during a particular time period. I have the following columns: LastName, Gender, and DateConfined (along with others that need not be used) My report shows (Example) Jones, AA, 10/04/04 Jones, AA, 10/0704 Last, AA. 09/17/04 Last, AA, 10/04/04 All my counts, sums, or abs end up with a total of 4(four). I need the report to only show 2 (two) counts of ethnicity because I need not to count the duplicate individuals. I have removed the duplication of the last name, but counting the LastName seems to not work either. I have tried various counts, sums, abs, but still getting the total number not the total number minus duplications. Thanks in advance for the help |
#2
|
|||
|
|||
You can create a grouping level with a header (could be very short) on
LastName,... Add a text box to the group header Name: txtCount Control Source: =1 Running Sum: Over Report Then add a text box to the report footer Control Source: =txtCount -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I have a report where I need to count the number of individuals confined by ethnicity. In this report I can not count not individual who confined more than once during a particular time period. I have the following columns: LastName, Gender, and DateConfined (along with others that need not be used) My report shows (Example) Jones, AA, 10/04/04 Jones, AA, 10/0704 Last, AA. 09/17/04 Last, AA, 10/04/04 All my counts, sums, or abs end up with a total of 4(four). I need the report to only show 2 (two) counts of ethnicity because I need not to count the duplicate individuals. I have removed the duplication of the last name, but counting the LastName seems to not work either. I have tried various counts, sums, abs, but still getting the total number not the total number minus duplications. Thanks in advance for the help |
#3
|
|||
|
|||
I tried your suggestion and I was comming up with a total not subtotals.
Maybe I explained it wrong. I have LastName, Gender, DateConfined A, AA, 10/10/97 A, AA, 11/12/97 B, CA, 01/05/97 C, AS, 02/03/98 C, AS, 03/05/98 My report needs to let me know that there are 2(two) AA 1(one) CA 2(two) AS I can only achieve a complete total of 5(five)Individuals . Some How I need to group the gender and hide duplicates of last name and count the number of genders not duplicting the last name. Hope you can help. Regina "Duane Hookom" wrote: You can create a grouping level with a header (could be very short) on LastName,... Add a text box to the group header Name: txtCount Control Source: =1 Running Sum: Over Report Then add a text box to the report footer Control Source: =txtCount -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I have a report where I need to count the number of individuals confined by ethnicity. In this report I can not count not individual who confined more than once during a particular time period. I have the following columns: LastName, Gender, and DateConfined (along with others that need not be used) My report shows (Example) Jones, AA, 10/04/04 Jones, AA, 10/0704 Last, AA. 09/17/04 Last, AA, 10/04/04 All my counts, sums, or abs end up with a total of 4(four). I need the report to only show 2 (two) counts of ethnicity because I need not to count the duplicate individuals. I have removed the duplication of the last name, but counting the LastName seems to not work either. I have tried various counts, sums, abs, but still getting the total number not the total number minus duplications. Thanks in advance for the help |
#4
|
|||
|
|||
Create a totals query or queries that generate your values and base your
report on the new query. -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I tried your suggestion and I was comming up with a total not subtotals. Maybe I explained it wrong. I have LastName, Gender, DateConfined A, AA, 10/10/97 A, AA, 11/12/97 B, CA, 01/05/97 C, AS, 02/03/98 C, AS, 03/05/98 My report needs to let me know that there are 2(two) AA 1(one) CA 2(two) AS I can only achieve a complete total of 5(five)Individuals . Some How I need to group the gender and hide duplicates of last name and count the number of genders not duplicting the last name. Hope you can help. Regina "Duane Hookom" wrote: You can create a grouping level with a header (could be very short) on LastName,... Add a text box to the group header Name: txtCount Control Source: =1 Running Sum: Over Report Then add a text box to the report footer Control Source: =txtCount -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I have a report where I need to count the number of individuals confined by ethnicity. In this report I can not count not individual who confined more than once during a particular time period. I have the following columns: LastName, Gender, and DateConfined (along with others that need not be used) My report shows (Example) Jones, AA, 10/04/04 Jones, AA, 10/0704 Last, AA. 09/17/04 Last, AA, 10/04/04 All my counts, sums, or abs end up with a total of 4(four). I need the report to only show 2 (two) counts of ethnicity because I need not to count the duplicate individuals. I have removed the duplication of the last name, but counting the LastName seems to not work either. I have tried various counts, sums, abs, but still getting the total number not the total number minus duplications. Thanks in advance for the help |
#5
|
|||
|
|||
Duane,
I did as you suggested and I succeeded. I told my boss that when adding the additional fields (which have duplicates of their own) makes the errors. thanks for the help. "Duane Hookom" wrote: Create a totals query or queries that generate your values and base your report on the new query. -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I tried your suggestion and I was comming up with a total not subtotals. Maybe I explained it wrong. I have LastName, Gender, DateConfined A, AA, 10/10/97 A, AA, 11/12/97 B, CA, 01/05/97 C, AS, 02/03/98 C, AS, 03/05/98 My report needs to let me know that there are 2(two) AA 1(one) CA 2(two) AS I can only achieve a complete total of 5(five)Individuals . Some How I need to group the gender and hide duplicates of last name and count the number of genders not duplicting the last name. Hope you can help. Regina "Duane Hookom" wrote: You can create a grouping level with a header (could be very short) on LastName,... Add a text box to the group header Name: txtCount Control Source: =1 Running Sum: Over Report Then add a text box to the report footer Control Source: =txtCount -- Duane Hookom MS Access MVP "artregdlg" wrote in message ... I have a report where I need to count the number of individuals confined by ethnicity. In this report I can not count not individual who confined more than once during a particular time period. I have the following columns: LastName, Gender, and DateConfined (along with others that need not be used) My report shows (Example) Jones, AA, 10/04/04 Jones, AA, 10/0704 Last, AA. 09/17/04 Last, AA, 10/04/04 All my counts, sums, or abs end up with a total of 4(four). I need the report to only show 2 (two) counts of ethnicity because I need not to count the duplicate individuals. I have removed the duplication of the last name, but counting the LastName seems to not work either. I have tried various counts, sums, abs, but still getting the total number not the total number minus duplications. Thanks in advance for the help |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Counting duplicates\Frequency of duplicates | A.D. | Worksheet Functions | 3 | June 30th, 2004 04:59 PM |
Text String Duplicates | Frank Kabel | Worksheet Functions | 0 | February 20th, 2004 06:56 PM |
Formula Calculating Minus Values | Russ | Worksheet Functions | 2 | October 6th, 2003 03:06 PM |
Counting Names in a column | jcarlon | Worksheet Functions | 3 | October 2nd, 2003 03:47 PM |