Help me count records without duplicates

[psandrew]

In the group footer I am trying to count the number of records in the report
without counting records with duplicate data in the summary field. Here is
the basic report structure with grouping that I have:

Area: Plot: Part: Quantity:

Osborn 1 NE 10
NW 5
E 15
Total For Osborn Plot 1: 30
3 W 25
SE 20
Total For Osborn Plot 2: 40
Total For Osborn Area (in 2 Plots): 70

.......... The expression in the parenthesis is what I am having problems
with; instead of counting two unique plots, it currently counts the total
number of records including duplicates, with non-null entries in the Plot
field. This gives me the result of : ..."(in 5 Plots)". I have attempted to
solve this by trying different combinations of the hide duplicates and the
running sum options in the footer text box's design properties window but
with no change. The corresponding portion of the control source is: ... & "
(" & Count(*) & " " & IIf(Count(*)=1,"Plot","Plots") & ")" .

I would really appreciate a solution for this. thanks a lot

 

[strive4peace]

In the Plot Group header section, make a calculated control

Name --> CountPlots
ControlSource --> =1
RunningSum --> Over Group

In the Area Footer, the calculated textbox control for the
"label" would be:

ControlSource -->
=" Total For "
& [Area_controlname]
& " Area (in "
& CountPlots
& " Plots):

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[psandrew]

Ok, with the running sum setting at "Over Group" on the new calculated
control in the Plot Group header I get a result of (in 1 plot). If I set the
running sum to "Over All" I get the correct result of (in 2 plots). However
in the next area group of the report, Schneider Area, the result adds the
plots from the Osborn Area into its own plot count. The Schnieder area has 3
plots of its own, but the result adds the 2 from the Osborn area to display
an incorrect 5 plots. What am I missing?

In the Plot Group header section, make a calculated control

Name --> CountPlots
ControlSource --> =1
RunningSum --> Over Group

In the Area Footer, the calculated textbox control for the
"label" would be:

ControlSource -->
=" Total For "
& [Area_controlname]
& " Area (in "
& CountPlots
& " Plots):

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

In the group footer I am trying to count the number of records in the report
without counting records with duplicate data in the summary field. Here is
the basic report structure with grouping that I have:

Area: Plot: Part: Quantity:

Osborn 1 NE 10
NW 5
E 15
Total For Osborn Plot 1: 30
3 W 25
SE 20
Total For Osborn Plot 2: 40
Total For Osborn Area (in 2 Plots): 70

......... The expression in the parenthesis is what I am having problems
with; instead of counting two unique plots, it currently counts the total
number of records including duplicates, with non-null entries in the Plot
field. This gives me the result of : ..."(in 5 Plots)". I have attempted to
solve this by trying different combinations of the hide duplicates and the
running sum options in the footer text box's design properties window but
with no change. The corresponding portion of the control source is: ... & "
(" & Count(*) & " " & IIf(Count(*)=1,"Plot","Plots") & ")" .

I would really appreciate a solution for this. thanks a lot

 

[strive4peace]

What are your sorting and grouping levels?


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Ok, with the running sum setting at "Over Group" on the new calculated
control in the Plot Group header I get a result of (in 1 plot). If I set the
running sum to "Over All" I get the correct result of (in 2 plots). However
in the next area group of the report, Schneider Area, the result adds the
plots from the Osborn Area into its own plot count. The Schnieder area has 3
plots of its own, but the result adds the 2 from the Osborn area to display
an incorrect 5 plots. What am I missing?

:

In the Plot Group header section, make a calculated control

Name --> CountPlots
ControlSource --> =1
RunningSum --> Over Group

In the Area Footer, the calculated textbox control for the
"label" would be:

ControlSource -->
=" Total For "
& [Area_controlname]
& " Area (in "
& CountPlots
& " Plots):

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

In the group footer I am trying to count the number of records in the report
without counting records with duplicate data in the summary field. Here is
the basic report structure with grouping that I have:

Area: Plot: Part: Quantity:

Osborn 1 NE 10
NW 5
E 15
Total For Osborn Plot 1: 30
3 W 25
SE 20
Total For Osborn Plot 2: 40
Total For Osborn Area (in 2 Plots): 70

......... The expression in the parenthesis is what I am having problems
with; instead of counting two unique plots, it currently counts the total
number of records including duplicates, with non-null entries in the Plot
field. This gives me the result of : ..."(in 5 Plots)". I have attempted to
solve this by trying different combinations of the hide duplicates and the
running sum options in the footer text box's design properties window but
with no change. The corresponding portion of the control source is: ... & "
(" & Count(*) & " " & IIf(Count(*)=1,"Plot","Plots") & ")" .

I would really appreciate a solution for this. thanks a lot