Don't sum duplicates

R

Robert_L_Ross

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
A

Al Camp

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
 
R

Robert_L_Ross

Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob
 
A

Al Camp

I should have been a bit clearer. I meant for you to leave your original BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so each one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

Al Camp said:
Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
R

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?


Al Camp said:
I should have been a bit clearer. I meant for you to leave your original BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so each one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

Al Camp said:
Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
A

Al Camp

Robert,
Not the way you have your tables set up. Batch 124 has a batch count of 50 yet you
repeat the 50 for each person involved, and in doing so, imply that Batch 124 has a 150
Count. You can't now go back and try to ignore that data... it's in there.
Each BatchID should be a "one" record. The people who work on those batches and the
qty they complete should be in another "many" table. (ex. tblBacthWork) One BatchID
with many transactions against it, related via the BatchID key field.
Hiding duplicates is only "visual"... each duplicate BatchCount is still there.

Using that method, you can report....
124 04/17/06 50 (BatchID Header)
-----------------------------------------------
0 Sam (detail)
0 Frank (detail)
50 Bob (detail)
----------------------------------------------
124 50 50 BatchID Footer (summing)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
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?


Al Camp said:
I should have been a bit clearer. I meant for you to leave your original
BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so each
one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your
report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

:

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
R

Robert_L_Ross

Al,

BatchID is a one to many already.

Here is my table set up:
tblBatchControl
BatchID (primary key)
Batch Count (Int)
...other fields (batch type, etc.)

This has a one to many relationship on BatchID to the following table:
tblBatchWork
BatchWorkID (primary key auto number)
BatchID
WorkDate
Completed
Processor

My query has to display BatchID to show the specific batch to the report
requestor.

I need this report to show the batch ID and all of it's work history, but
only to show the BatchCount once, but som the Completed.

Sorry for the confusion, I should have put the table layout earlier in the
postings.

So to answer your question, I do have the two tables set up as a one to many
off of batchID. However, I should note that the tables are on SQL, and I
don't have the relationship set up in the Relationships window, but rather in
the Query window (not sure if that makes a difference or not).

If you like, I can use www.tinyurl.com to hold screen shots of the db then I
can post those url's so you can 'see' what I see.

Al Camp said:
Robert,
Not the way you have your tables set up. Batch 124 has a batch count of 50 yet you
repeat the 50 for each person involved, and in doing so, imply that Batch 124 has a 150
Count. You can't now go back and try to ignore that data... it's in there.
Each BatchID should be a "one" record. The people who work on those batches and the
qty they complete should be in another "many" table. (ex. tblBacthWork) One BatchID
with many transactions against it, related via the BatchID key field.
Hiding duplicates is only "visual"... each duplicate BatchCount is still there.

Using that method, you can report....
124 04/17/06 50 (BatchID Header)
-----------------------------------------------
0 Sam (detail)
0 Frank (detail)
50 Bob (detail)
----------------------------------------------
124 50 50 BatchID Footer (summing)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
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?


Al Camp said:
I should have been a bit clearer. I meant for you to leave your original
BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so each
one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your
report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

:

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
A

Al Camp

Robert,
Did you try my last suggestion/report layout?
If your tables are set up that way, then my last suggestion should work...
(do not include BatchCount in the detail section.)
BatchCountValue ^ ^Sum(Comp)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

BatchID is a one to many already.

Here is my table set up:
tblBatchControl
BatchID (primary key)
Batch Count (Int)
...other fields (batch type, etc.)

This has a one to many relationship on BatchID to the following table:
tblBatchWork
BatchWorkID (primary key auto number)
BatchID
WorkDate
Completed
Processor

My query has to display BatchID to show the specific batch to the report
requestor.

I need this report to show the batch ID and all of it's work history, but
only to show the BatchCount once, but som the Completed.

Sorry for the confusion, I should have put the table layout earlier in the
postings.

So to answer your question, I do have the two tables set up as a one to many
off of batchID. However, I should note that the tables are on SQL, and I
don't have the relationship set up in the Relationships window, but rather in
the Query window (not sure if that makes a difference or not).

If you like, I can use www.tinyurl.com to hold screen shots of the db then I
can post those url's so you can 'see' what I see.

Al Camp said:
Robert,
Not the way you have your tables set up. Batch 124 has a batch count of 50 yet you
repeat the 50 for each person involved, and in doing so, imply that Batch 124 has a 150
Count. You can't now go back and try to ignore that data... it's in there.
Each BatchID should be a "one" record. The people who work on those batches and the
qty they complete should be in another "many" table. (ex. tblBacthWork) One BatchID
with many transactions against it, related via the BatchID key field.
Hiding duplicates is only "visual"... each duplicate BatchCount is still there.

Using that method, you can report....
124 04/17/06 50 (BatchID Header)
-----------------------------------------------
0 Sam (detail)
0 Frank (detail)
50 Bob (detail)
----------------------------------------------
124 50 50 BatchID Footer (summing)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
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 should have been a bit clearer. I meant for you to leave your original
BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so
each
one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of
the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because
they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your
report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

:

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
R

Robert_L_Ross

From what I can tell of your post, that would require adding another grouping
level to the report. The problem with that is that 90% of the lines only
have one entry...so that would mean for each batch I would have 2 lines (the
batch info then the footer) even though it's only required on 10% of the
lines...making my report much longer than it needs to be.

Isn't there some way I can put code in the report detail header that would
only show that value the first or last time it appears on the report? I seem
to remember some code that someone posted a while back that they used to
alternate background colors of records (like the old green and white impact
printer paper used to be like).

Al Camp said:
Robert,
Did you try my last suggestion/report layout?
If your tables are set up that way, then my last suggestion should work...
(do not include BatchCount in the detail section.)
BatchCountValue ^ ^Sum(Comp)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

BatchID is a one to many already.

Here is my table set up:
tblBatchControl
BatchID (primary key)
Batch Count (Int)
...other fields (batch type, etc.)

This has a one to many relationship on BatchID to the following table:
tblBatchWork
BatchWorkID (primary key auto number)
BatchID
WorkDate
Completed
Processor

My query has to display BatchID to show the specific batch to the report
requestor.

I need this report to show the batch ID and all of it's work history, but
only to show the BatchCount once, but som the Completed.

Sorry for the confusion, I should have put the table layout earlier in the
postings.

So to answer your question, I do have the two tables set up as a one to many
off of batchID. However, I should note that the tables are on SQL, and I
don't have the relationship set up in the Relationships window, but rather in
the Query window (not sure if that makes a difference or not).

If you like, I can use www.tinyurl.com to hold screen shots of the db then I
can post those url's so you can 'see' what I see.

Al Camp said:
Robert,
Not the way you have your tables set up. Batch 124 has a batch count of 50 yet you
repeat the 50 for each person involved, and in doing so, imply that Batch 124 has a 150
Count. You can't now go back and try to ignore that data... it's in there.
Each BatchID should be a "one" record. The people who work on those batches and the
qty they complete should be in another "many" table. (ex. tblBacthWork) One BatchID
with many transactions against it, related via the BatchID key field.
Hiding duplicates is only "visual"... each duplicate BatchCount is still there.

Using that method, you can report....
124 04/17/06 50 (BatchID Header)
-----------------------------------------------
0 Sam (detail)
0 Frank (detail)
50 Bob (detail)
----------------------------------------------
124 50 50 BatchID Footer (summing)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


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 should have been a bit clearer. I meant for you to leave your original
BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so
each
one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of
the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because
they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your
report,
so I can get a better idea how to assist you. But I think this is primarily a table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

:

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 
A

Al Camp

Robert,
I have a solution...
1. Add an "unbound" field to your report Detail called TempBatchCount. Move BatchCount
(hidden) off to the side and slide TempBatchCount into it's place in your data layout.
2. Add another unbound Detail field called RunSumTempBatchCount. Set it to...
=TempBatchCount (make it a running sum OverAll)
3. In your ReportFooter place two fields...
TotalTempBatchCount = RunSumTempBatchCount
TotalCompleted =Sum(Completed)

Now add this code to the report module...

Option Compare Database
Option Explicit
Dim DetailCount As Byte
------------------------------------
Private Sub BatchCountHeader_Format(Cancel As Integer, FormatCount As Integer)
DetailCount = 0
End Sub
----------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DetailCount <> 0 Then
TempBatchCount = 0
Else
TempBatchCount = BatchCount
End If
DetailCount = DetailCount + 1
End Sub

This works...
If you want, contact me through my website below, and I'll send a sample report. What
version of Access are you using?
Post back here if you contact me... I have a very aggresive spam filter, and you may get
bounced.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
From what I can tell of your post, that would require adding another grouping
level to the report. The problem with that is that 90% of the lines only
have one entry...so that would mean for each batch I would have 2 lines (the
batch info then the footer) even though it's only required on 10% of the
lines...making my report much longer than it needs to be.

Isn't there some way I can put code in the report detail header that would
only show that value the first or last time it appears on the report? I seem
to remember some code that someone posted a while back that they used to
alternate background colors of records (like the old green and white impact
printer paper used to be like).

Al Camp said:
Robert,
Did you try my last suggestion/report layout?
If your tables are set up that way, then my last suggestion should work...
(do not include BatchCount in the detail section.)
Using that method, you can report.... BID Date B/C
124 04/17/06 50 Comp Proc. (BatchID Header)
BatchCountValue ^ ^Sum(Comp)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Robert_L_Ross said:
Al,

BatchID is a one to many already.

Here is my table set up:
tblBatchControl
BatchID (primary key)
Batch Count (Int)
...other fields (batch type, etc.)

This has a one to many relationship on BatchID to the following table:
tblBatchWork
BatchWorkID (primary key auto number)
BatchID
WorkDate
Completed
Processor

My query has to display BatchID to show the specific batch to the report
requestor.

I need this report to show the batch ID and all of it's work history, but
only to show the BatchCount once, but som the Completed.

Sorry for the confusion, I should have put the table layout earlier in the
postings.

So to answer your question, I do have the two tables set up as a one to many
off of batchID. However, I should note that the tables are on SQL, and I
don't have the relationship set up in the Relationships window, but rather in
the Query window (not sure if that makes a difference or not).

If you like, I can use www.tinyurl.com to hold screen shots of the db then I
can post those url's so you can 'see' what I see.

:

Robert,
Not the way you have your tables set up. Batch 124 has a batch count of 50 yet
you
repeat the 50 for each person involved, and in doing so, imply that Batch 124 has a
150
Count. You can't now go back and try to ignore that data... it's in there.
Each BatchID should be a "one" record. The people who work on those batches and
the
qty they complete should be in another "many" table. (ex. tblBacthWork) One
BatchID
with many transactions against it, related via the BatchID key field.
Hiding duplicates is only "visual"... each duplicate BatchCount is still there.

Using that method, you can report....
124 04/17/06 50 (BatchID Header)
-----------------------------------------------
0 Sam (detail)
0 Frank (detail)
50 Bob (detail)
----------------------------------------------
124 50 50 BatchID Footer (summing)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


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 should have been a bit clearer. I meant for you to leave your original
BatchField
Sum in the query, but add a separate calculated, bound field, to the query...
WorkDate BatchCount FirstBC : BatchCount
GroupBy Sum First
But... that will not work because each record has a different Batch ID... so
each
one
will yield a "first" value.

On further review of your initial post, where you indicate that...
since I may have two records that have different BatchID's but the same
BatchCount.
This indicates to me that the BatchCount is associated with the "one" side of
the
relationship, not the "many", and some table redesign is in order.
In other words, the reason you don't want to add "each" BatchCount is because
they
really didn't "each" have that value.

To be sure, I'd need to see 2 or 3 sample records as they now appear on your
report,
so I can get a better idea how to assist you. But I think this is primarily a
table
design problem...
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Al,

No dice buddy, I put this into the BatchCount field source:
=First([BatchCount])
Unfortunately it gives me the first value of that RecdDate, (groupings are
by RecdDate, ShortText, WorkDate then BatchID, with headers and footers on
RecdDate, ShortText and footers only on BatchID).

I'm thinking it has to be something like a 'virtual field' in the detail
that is a count of the current record. I seem to remember seeing that code
on this newsgroup before (I think you click on the Detail and put code there,
then put a field in that calls to that virtual value)...where if the value
was <1 you included X value (in my case it would be to put in the BatchCount
value if the virtual field value is greater than 1).

Has anyone seen that code or used it before?

THX for the suggestion on that Al...to you and anyone else, let me know if
you have any other ideas!

Rob

:

Robert,
If BatchCount repeats the same amount, I "think" I would try
First(BatchCount) (or Max or Min, etc...) to yield just one value.
Then you could sum or runningsum that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a report with the following output fields:
Batch ID
WorkDate
BatchCount
Completed
CompletedToDate
Processor
MinNeeded

When the underlying query pulls multiple batch ID's issued on the same Work
Date, I need to display both lines, but I only need to display the
BatchCount
for one record.

How do I get the field in the detail section to populate as 0 or null on all
but one record if the BatchID is a duplicate? I have the BatchID set to
hide
duplicates, but I don't think I can do that on BatchCount since I may have
two records that have different BatchID's but the same BatchCount.

Any ideas? I know you brainiacs that live and breate Access have seen this
before, I'm just not sure I'm wording it right.

Thanks in advance for reading and twice if you can reply!

Rob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top