Total & Sequence #

  • Thread starter Thread starter A Hopper
  • Start date Start date
A

A Hopper

Total:
On my report I have a text Box for "Mill Roll #" and a
text box for "Total". If there is only one "Mill Roll #"
then I want the same number in the "Grand Total" text box.
If there is more than one record for the "Mill Roll #" I
want to add the "Total"(s) together and put the answer in
the "Grand Total" text box only once. (preferably across
from the last "Mill Roll #" in the list of "Mill Roll
#"(s) that are the same number - as below)

Example
Mill Roll # Total Grand Total

WP 6088 6088

234113C 5882 5882

232814B 3883
232814B 4812 8695

Sequence:
On the same report I would like to add a "Sequence #"
starting at 1 and ascending with each unique Mill Roll #.

Example
Sequence# Mill Roll# Total Grand Total

1 WP 6088 6088

2 234113C 5882 5882

232814B 3883
3 232814B 4812 8695

Thank you for your help

Allan
 
A said:
Total:
On my report I have a text Box for "Mill Roll #" and a
text box for "Total". If there is only one "Mill Roll #"
then I want the same number in the "Grand Total" text box.
If there is more than one record for the "Mill Roll #" I
want to add the "Total"(s) together and put the answer in
the "Grand Total" text box only once. (preferably across
from the last "Mill Roll #" in the list of "Mill Roll
#"(s) that are the same number - as below)

Example
Mill Roll # Total Grand Total

WP 6088 6088

234113C 5882 5882

232814B 3883
232814B 4812 8695

Sequence:
On the same report I would like to add a "Sequence #"
starting at 1 and ascending with each unique Mill Roll #.

Example
Sequence# Mill Roll# Total Grand Total

1 WP 6088 6088

2 234113C 5882 5882

232814B 3883
3 232814B 4812 8695
Click to expand...

OK, I think I came up with a way to do this. I hope I can
explain it to you.

First, use Sorting and Grouping to create a Mill Roll group
with header and footer sections.

Next, we'll try to get the sequence number and grand total
in the group footer section. Add a text box for the
sequence number, set its control source expression to =1 and
its RunningSum property to Over All. Add another text box
for the grand total and set its control source expression to
=Sum(totalfield).

Test the report to see if the footer displays the correct
values in the desired horizontal positions (we'll work on
the vertical position next).

With that part working, we now have to get those totals on
the same line as the last detail. This means we have to
have a way to identify the last record. This is done by
adding a text box to the group header section. Name this
text box txtGroupLines and set its control source expression
to =Count(*). You can set the header section's Visible
property to No so it doesn't show in the report.

Now we need to be able to identify each detail to see if its
the last one, so add a text box named txtLineNum to the
detail section. Set its control source to =1 and its
Running Sum property to Over Group.

Finally, use a little code to cause the group footer section
to overlay the last detail. Add this line of code to the
detail section's Format event procedure:

Me.MoveLayout = Not (Me.txtLineNum = Me.txtGroupLines)
 
Marshal, sorry for the late reply. I have worked on this
report for about a day now. The job became more
complicated as I worked on it. Two other factors came into
play. There is a "Low Number" field that needs to be
sorted ascending (main sorting field) and a "Skid Number"
field. So the result looks like. When I group on "Low
Number" and sort I am not able to combine the "Mill Roll
#" to get one total.

Example
I have been able to get the "Grand Total" for each record
but not the combined total as in the last two records of
the example. I get a "Grand Total" for each record.

Thank you very much for your help
Allan
 
A said:
Marshal, sorry for the late reply. I have worked on this
report for about a day now. The job became more
complicated as I worked on it. Two other factors came into
play. There is a "Low Number" field that needs to be
sorted ascending (main sorting field) and a "Skid Number"
field. So the result looks like. When I group on "Low
Number" and sort I am not able to combine the "Mill Roll
#" to get one total.

Example

I have been able to get the "Grand Total" for each record
but not the combined total as in the last two records of
the example. I get a "Grand Total" for each record.
Click to expand...

You lost me. If your top level sort field is Low Number,
then where is the grand total for a Mill supposed to be
displayed?

Can a single Mill Roll # appear under more than one Low
Number?

How does the Skid Number fit into the sort order?

What do you mean when you say you get a grand total for each
record? I thought each record has a total and the grand
total was for each Mill Roll??

Did you eliminate the Sequence Number?
 
Back
Top