Can I ignore duplicates when counting records and trying to get a

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a query that combines two tables. In this report I
have several grouping levels which is throwing off the summary counts and
sums I am trying to do after each section. For instance (in a simplified
format):

Trend Status: Final
(Trend Status Header)
Trend No RFI Trend Amount (Trend
No Header)
1 100
2
(Detail)
4
6
2 100
3
Trends = 2 Sum = 200 (This is what I want) (Trend
Status Footer)

Trends = 4 Sum = 400 (This is what I get)

All of the fields are in my tblCOLog except the "RFI" field - it is in tblRFI.

I am not familiar with SQL - I am willing to learnif I have to, or perhaps
there is a way to do this in the Report mode.

I would appreciate any advice.

Thanks.
 
Stacy101344 said:
I have a report based on a query that combines two tables. In this report I
have several grouping levels which is throwing off the summary counts and
sums I am trying to do after each section. For instance (in a simplified
format):

Trend Status: Final
(Trend Status Header)
Trend No RFI Trend Amount (Trend
No Header)
1 100
2
(Detail)
4
6
2 100
3
Trends = 2 Sum = 200 (This is what I want) (Trend
Status Footer)

Trends = 4 Sum = 400 (This is what I get)

All of the fields are in my tblCOLog except the "RFI" field - it is in tblRFI.

I am not familiar with SQL - I am willing to learnif I have to, or perhaps
there is a way to do this in the Report mode.
Click to expand...


The Count and Sum functions operate on all of the data
records. You want to operate only on the group data.

Add a text box named txtRunCnt to the group header for the
trend number. Set its control source expression to =1 and
RunningSum property to Over Group. Then the trend status
group footer can display the number of trends in a text box
with the expression =txtRunCnt

The total amount can be done similarly with a text box
that's bound to the Amount field.
 
Back
Top