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
