Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    5

    Question 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 16:02.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote 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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    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. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    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
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    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. #6
    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. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Great!

    The important thing is that you both figured it out AND let us know that you did so. Good stuff!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •