Count only individual records

[Robert_L_Ross]

Ok, here's where I'm at now...

I have a report with the following fields:
BatchID DateWorked BatchCount Completed Processor
123 04/14/06 50 0 Bob
124 04/14/06 50 0 Sam
124 04/17/06 50 0 Frank
124 04/17/06 50 50 Bob

I want to only show the batch Count on the first instance of that record
based off of BatchID:
BatchID DateWorked BatchCount Completed Processor
123 04/14/06 50 0 Bob
124 04/14/06 50 0 Sam
124 04/17/06 0 Frank
124 04/17/06 50 Bob

This way when I sum I get only the 50 Batch Count and the 50 Completed

I guess I'm asking how to supress the BatchCount when the report identifies
the BatchID is a duplicate in that group.

I've tried making a BatchID header, hiding it and inserting a field into the
hidden header that is set to "=1" then putting a value on each record to show
the record number...tried it setting Running Sum to Over All and Over Group.

My problem is when I do that I get the following:
# BatchID DateWorked BatchCount Completed Processor
1 123 04/14/06 50 0 Bob
2 124 04/14/06 50 0 Sam
2 124 04/14/06 50 0 Frank
2 124 04/14/06 50 50 Bob
3 125 04/14/06 100 50 Sue

I want it to do this:
# BatchID DateWorked BatchCount Completed Processor
1 123 04/14/06 50 0 Bob
1 124 04/14/06 50 0 Sam
2 124 04/14/06 50 0 Frank
3 124 04/14/06 50 50 Bob
1 125 04/14/06 100 50 Sue

This way, I can tell it to display Batch Count ONLY when the counter is 1,
display null if it's not. Right now when I sum on group I get batch 124
three times as high as it actually is.

Does this make sense?

Thanks guys!

 

[Marshall Barton]

Ok, here's where I'm at now...

I have a report with the following fields:
BatchID DateWorked BatchCount Completed Processor
123 04/14/06 50 0 Bob
124 04/14/06 50 0 Sam
124 04/17/06 50 0 Frank
124 04/17/06 50 50 Bob

I want to only show the batch Count on the first instance of that record
based off of BatchID:
BatchID DateWorked BatchCount Completed Processor
123 04/14/06 50 0 Bob
124 04/14/06 50 0 Sam
124 04/17/06 0 Frank
124 04/17/06 50 Bob

This way when I sum I get only the 50 Batch Count and the 50 Completed

I guess I'm asking how to supress the BatchCount when the report identifies
the BatchID is a duplicate in that group.

I've tried making a BatchID header, hiding it and inserting a field into the
hidden header that is set to "=1" then putting a value on each record to show
the record number...tried it setting Running Sum to Over All and Over Group.

My problem is when I do that I get the following:
# BatchID DateWorked BatchCount Completed Processor
1 123 04/14/06 50 0 Bob
2 124 04/14/06 50 0 Sam
2 124 04/14/06 50 0 Frank
2 124 04/14/06 50 50 Bob
3 125 04/14/06 100 50 Sue

I want it to do this:
# BatchID DateWorked BatchCount Completed Processor
1 123 04/14/06 50 0 Bob
1 124 04/14/06 50 0 Sam
2 124 04/14/06 50 0 Frank
3 124 04/14/06 50 50 Bob
1 125 04/14/06 100 50 Sue

This way, I can tell it to display Batch Count ONLY when the counter is 1,
display null if it's not. Right now when I sum on group I get batch 124
three times as high as it actually is.

Does this make sense?

I think maybe you want the running sum (Over Group) text box
in the detail section instead of the group header.

 

[Pat Hartman\(MVP\)]

When you have a 1-m relationship and you have data in the 1-side table that
you want to sum or count, you will need to use a main report for the 1-side
data and a subreport for the many-side data. If you just use a main report,
the RecordSource must join the 1-side and many-side tables and that has the
undesirable effect of "duplicating" the field you want to sum. If you
didn't need to sum the batch count, you could get by using the sorting and
grouping properties and hiding duplicate values. But hiding a duplicate
value doesn't get rid of it so the "duplicates" will be summed.

 

[Al Camp]

Robert,
Please don't multi-post the same question 3 times in one day. Multiple people are
posting replies to the individual posts with no continuity.
I responded to your previous "Don't sum duplicates." (04/14) I found (and tested) the
solution with a workaround. Check out that reply.