Calculate Total Books

[Guest]

I am preparing packing slips for boxes to be sent out. There are different
book orders going into the same box. Each book in the order will have its
own set of id numbers that are usually, but not always, in a string(101-121
etc).

I use a calculated field on the form to display the number of books in the
string as I store the start book number and subract it from the end book
number and add 1 to get the actual number of books in the string.

I use the same calculated field on the report to show the number of books in
each detial, but I have multiple strings in 1 box. I need to show a total
number of books in each box, but I cannot sum a calculated field in the
BoxNum footer.

How can I "count" the number of books in the box and display that particular
number in the BoxNum footer?

 

[Marshall Barton]

I am preparing packing slips for boxes to be sent out. There are different
book orders going into the same box. Each book in the order will have its
own set of id numbers that are usually, but not always, in a string(101-121
etc).

I use a calculated field on the form to display the number of books in the
string as I store the start book number and subract it from the end book
number and add 1 to get the actual number of books in the string.

I use the same calculated field on the report to show the number of books in
each detial, but I have multiple strings in 1 box. I need to show a total
number of books in each box, but I cannot sum a calculated field in the
BoxNum footer.

How can I "count" the number of books in the box and display that particular
number in the BoxNum footer?

If the start number and end number are in record source
fields (not calculated in txtboxes), try using a text box
expression like:

=Sum([end number]) - Sum([start number]) + Cpunt(*)

If the start bynber and end number are calculated values in
text boxes, then add a text box named txtRunStart with the
expression =txtStartNum and set its RunningSum property to
Over Group. Similarly for a txtRunEndNum text box. Then
the group footer text box expression would be:
=txtRunEndNum - txtRunStart + Count(*)

If that doesn't do what you want, you will have to provide
more infoemation.

 

[Guest]

I don't think I could use either of those as the book ids are all different.
eg.
Test Taken start end Total books (Calculated Field)
Test 1 101 133 33
Test 2 332 437 105
Test 3 5433 5490 58
Test 4 765 805 41
Total Books in the box: 236

I can fit a bunch of different test books in a box. I set up a table called
tblContents and it is hooked to other tables that fill in information. I
really only store numbers in the table contents. BoxId (auto#), BoxNum,
School(from tblSchools), Test (from tblTests), StartBook, Endbook, Notes, etc.

In the report I can get all the information into each detail, using BoxNum
as the primary sort for the report. I can determine the number of books in
each detail by using a calculated field.

I just want to be able to Sum all the books from the calculated fields in
the detail section into the BoxNum footer in the report.

If Access would just allow a Sum of a series of calculated fields all would
be great.

Does that give enough information?

--
Thanks As Always for any help
Rip

I am preparing packing slips for boxes to be sent out. There are different
book orders going into the same box. Each book in the order will have its
own set of id numbers that are usually, but not always, in a string(101-121
etc).

I use a calculated field on the form to display the number of books in the
string as I store the start book number and subract it from the end book
number and add 1 to get the actual number of books in the string.

I use the same calculated field on the report to show the number of books in
each detial, but I have multiple strings in 1 box. I need to show a total
number of books in each box, but I cannot sum a calculated field in the
BoxNum footer.

How can I "count" the number of books in the box and display that particular
number in the BoxNum footer?

If the start number and end number are in record source
fields (not calculated in txtboxes), try using a text box
expression like:

=Sum([end number]) - Sum([start number]) + Cpunt(*)

If the start bynber and end number are calculated values in
text boxes, then add a text box named txtRunStart with the
expression =txtStartNum and set its RunningSum property to
Over Group. Similarly for a txtRunEndNum text box. Then
the group footer text box expression would be:
=txtRunEndNum - txtRunStart + Count(*)

If that doesn't do what you want, you will have to provide
more infoemation.

 

[Marshall Barton]

I don't think I could use either of those as the book ids are all different.
eg.
Test Taken start end Total books (Calculated Field)
Test 1 101 133 33
Test 2 332 437 105
Test 3 5433 5490 58
Test 4 765 805 41
Total Books in the box: 236

I can fit a bunch of different test books in a box. I set up a table called
tblContents and it is hooked to other tables that fill in information. I
really only store numbers in the table contents. BoxId (auto#), BoxNum,
School(from tblSchools), Test (from tblTests), StartBook, Endbook, Notes, etc.

In the report I can get all the information into each detail, using BoxNum
as the primary sort for the report. I can determine the number of books in
each detail by using a calculated field.

I just want to be able to Sum all the books from the calculated fields in
the detail section into the BoxNum footer in the report.

If Access would just allow a Sum of a series of calculated fields all would
be great.

Did you try my expression that uses Sum? If not, was it
because you didn't understand the math? Think about this
simple example:
(a-x)+(b-y)+(c-z) is the same as (a+b+c)-(x+y+z)

The Access feature to sum calculated controls is the
RunningSum property.

Based on the additional information, my second suggestion
can get by with a single running sum text box with the
expression =totalbooks
But the other expression should work without the use of an
extra text box.

 

[Guest]

Thanks. I think I can figure it out with the sum function. I never thought
about the sum as adding all of the figures up and subtracting like you showed.

I learned something new tonight.