# Thread: Subtotal on main report based on line totals in subreport

1. Registered User
Join Date
Feb 2008
Posts
5

## Unanswered: Subtotal on main report based on line totals in subreport

I tried searching, but did not find anything on point with this basic problem.

I am trying to print out purchase orders, listing individual items, their prices, line totals after each line - subtotaling at the bottom of the report.

What I am getting is the subtotal from the next purchase order record, but it's displaying the prices and items from the current purchase order.

Here is the source for the per-line total:

Code:
`=[txtSUBQty]*[txtSUBItemPrice]`
Here is the source for the subtotal that DOES work on the subreport (for testing purposes):

Code:
`=Sum([intQuantityItems]*[intItemPrice])`
Here is the source for the subtotal back on the main report that reports the subtotal from the next purchase order set of items (next main record):

Code:
`=[frmPOItemByPOSub].[Report]![txtSUBSubTotal]`
I am utterly stumped on how to make this work... I know once I get the subtotal - I can do the math all on the main report - and the POs will be printable.

A definite extra is if I could report the final total back to the main table - but I would be happy just to make this work.

Thanks in advance for any help someone might have for me.
Last edited by Comrad_Durandal; 02-24-08 at 15:02.

2. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Originally Posted by Comrad_Durandal
I am trying to print out purchase orders, listing individual items, their prices, line totals after each line - subtotaling at the bottom of the form.

Here is the source for the subtotal that DOES work on the subreport (for testing purposes):

Here is the source for the subtotal back on the main form that reports the subtotal from the next purchase order set of items (next main record):

I am utterly stumped on how to make this work... I know once I get the subtotal - I can do the math all on the main form - and the POs will be printable.

A definite extra is if I could report the final total back to the main table - but I would be happy just to make this work.
My first question is "Is this a report or a form you're trying to work with?". I'm guessing it's a form.

Can you post the db or part thereof that exhibits the problem? The expressions you have seem to be all good and therefore I have no clue yet as to why your report/form total is showing that of the next record.

And there is no reason whatsoever to get the total back and store it in a table. That will just create a few more problems for you and give you almost zero benefit.

3. Registered User
Join Date
Feb 2008
Posts
5
I realize once you bolded that I was using form and report interchangably - which was my mistake. Sorry, I did mean report. I changed the post so reflect this, and it's not your fault that I was unclear. It's what I get for posting after midnight on a work day.

Anyway, yeah - this is a report. Ultimately, I do need to write up a form for entering purchase orders - and I would love it to total from a subform there as well - but that is another issue.

I can't post the entire database (due to ownership and disclosure issues) but I can give you the query that generates the main report, as well as the query that generates the subreport.

Main report query:
Code:
```SELECT tblPurchaseOrders.intPOID, tblPurchaseOrders.intDateEntered, tblPurchaseOrders.intVendorID, tblVendorMaster.strVendorName, tblVendorMaster.strVendorAddyOne, tblVendorMaster.strVendorAddyTwo, tblUSZipCodes.strCityName, tblUSZipCodes.strStateAbbrev, tblUSZipCodes.strZipCode, tblVendorMaster.strZipPlusFour, tblVendorMaster.strPhoneNumber, tblVendorMaster.strFaxNumber, tblPurchaseOrders.fltTaxAdd, tblPurchaseOrders.intPurchaseTotal, tblPurchaseOrders.strShipmentMethod, tblPurchaseOrders.strPaymentTerms
FROM (tblPurchaseOrders INNER JOIN tblVendorMaster ON tblPurchaseOrders.intVendorID = tblVendorMaster.intVendorID) INNER JOIN tblUSZipCodes ON tblVendorMaster.intVendorZipID = tblUSZipCodes.intZipID
ORDER BY tblPurchaseOrders.intDateEntered;```
Subreport query:
Code:
```SELECT tblPOItems.intPOItemID, tblPurchaseOrders.intPOID, tblPOItems.intQuantityItems, tblPOItems.intItemUPC, tblPOItems.strItemManufacturer, tblPOItems.strItemDescription, tblPOItems.intItemPrice
FROM tblPOItems INNER JOIN tblPurchaseOrders ON tblPOItems.intPONumber=tblPurchaseOrders.intPOID;```
The subreport is tied to the main form with the intPOID field present in the tblPOItems and tblPurchaseOrders table.

Thank you in advance for the reply - and the help.

4. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Ok... thanks for the clarification

The subreport is tied to the main form with the intPOID field present in the tblPOItems and tblPurchaseOrders table.
Posting after midnight again??

I couldn't resist

Anyway, I don't know how much more help I can be on this. The queries look ok, the expressions look ok... I can't see any reason why your total might return the total for the next record :/

Maybe it has something to do with WHERE you are putting these objects on the report... and where you might have a [page break before section] type definition.

Can you make a new database and import only enough objects into it that show the error and then post that? Even if you fudge the data to deal with confidentiality?

What does the LAST report page show the total of??

Interesting problem

Oh, and BTW, making a form/subform to do the same as the report is really easy. In fact, what I normally do is make the form/subform FIRST and then I use those to start making the report, because you can save a form as a report

Cheers

ST

5. Registered User
Join Date
Feb 2008
Posts
5
The last report shows the total for the last report's subreport material - it's really strange. I will see what I can get away with posting - I will also try something different in terms of design.

It's a occupational hazard of going into management... forms and reports get merged in the mind... that is why I resort to drinking.

6. Registered User
Join Date
Feb 2008
Posts
5
I GOT IT! Basically - where I was going wrong is I was putting the subtotal, taxes, and final total line in the report footer section, versus in the details section. When I got rid of the page footer and report footer - calculation works perfectly!

Thanks for the sounding board, it really helps to have a fresh set of eyes look at things to make sure I haven't completely lost my mind (just mostly there...)

7. L33t Helpa Munky
Join Date
Nov 2007
Location