Results 1 to 5 of 5

Thread: Subreports

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Cool Unanswered: Subreports

    I have a Budget database in Access 2007 in which I trying to get the sum of expenses back onto the main report. I have an encumbered amount on the main report and I want to bring the sum of the expenses (which are located on my subreport) and subtract them from the encumbered amount of the main report. When I run my subreport it totals all expenses perfectly and is linked by SAC's items. When I bring my total from the subreport to the main report and subtract the txtbudgetsum (encumberedamount) - sactotal (from the subreport) it will only subtract the first purchase order amount, will not subtract the total purchase orders even though on my subreport the total is correct. Do I have to sum all the expenses again when I bring it back onto the main report or can I just refer the name of the field on the subreport. I have been trying several scenarios from including "Bring the total from a subreport back onto the main report" from Allen Browne with no avail. Thanks in advance.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums



    Perhaps you could help us out by posting what code you've already done, where you're having problems, and any other information we'll need to answer your question. Then hopefully someone can lend you a hand.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    3

    Red face Subreports

    Thanks for the welcoming...

    Here is the SQL of the main form:

    SELECT DISTINCTROW tblbudget1.CostCenter, tblbudget1.Dept, tblbudget1.budgetyear, tblbudget1.date, tblbudget1.SAC, tblbudget1.Moneytype, tblbudget1.Adjustment, Sum(tblbudget1.Moneyamt) AS SumOfMoneyamt, tblbudget1.Quarter
    FROM tblbudget1
    GROUP BY tblbudget1.CostCenter, tblbudget1.Dept, tblbudget1.budgetyear, tblbudget1.date, tblbudget1.SAC, tblbudget1.Moneytype, tblbudget1.Adjustment, tblbudget1.Quarter
    ORDER BY tblbudget1.date;

    This is the SQL of the subform:

    SELECT DISTINCTROW [tblpurchaseorder Query].OaksVendorNo, [tblpurchaseorder Query].[Vendor Name], [tblpurchaseorder Query].PurchaseOrder, [tblpurchaseorder Query].tblpurchaseorder_Year, [tblpurchaseorder Query].SACItem, [tblpurchaseorder Query].Encumbered, [tblpurchaseorder Query].RecTicketNo, [tblpurchaseorder Query].PurchaseOrderNumber, [tblpurchaseorder Query].DVtype, [tblpurchaseorder Query].InvoiceNo, [tblpurchaseorder Query].VoucherNo, [tblpurchaseorder Query].MaterialRecDate, tblticketdetails.Quantity, [quantity]*[priceperunit] AS TotalCost, tblticketdetails.PricePerUnit, tblticketdetails.Unit, tblticketdetails.Comments, tblticketdetails.Description, Sum(tblmodification.ModAmount) AS SumOfModAmount
    FROM ([tblpurchaseorder Query] LEFT JOIN tblticketdetails ON [tblpurchaseorder Query].RecTicketNo = tblticketdetails.RecTicketNo) LEFT JOIN tblmodification ON [tblpurchaseorder Query].PurchaseOrder = tblmodification.PurchaseOrder
    GROUP BY [tblpurchaseorder Query].OaksVendorNo, [tblpurchaseorder Query].[Vendor Name], [tblpurchaseorder Query].PurchaseOrder, [tblpurchaseorder Query].tblpurchaseorder_Year, [tblpurchaseorder Query].SACItem, [tblpurchaseorder Query].Encumbered, [tblpurchaseorder Query].RecTicketNo, [tblpurchaseorder Query].PurchaseOrderNumber, [tblpurchaseorder Query].DVtype, [tblpurchaseorder Query].InvoiceNo, [tblpurchaseorder Query].VoucherNo, [tblpurchaseorder Query].MaterialRecDate, tblticketdetails.Quantity, [quantity]*[priceperunit], tblticketdetails.PricePerUnit, tblticketdetails.Unit, tblticketdetails.Comments, tblticketdetails.Description;


    I have the two forms linked by SAC/SACItem. I want to take the encumbered amount from the main report and subtract the sum of the SACtotal from the subreport. The reports run perfectly individually, but when I embed the sub report it will only subtract the first SAC total from the encumbered amount. Any help would be appreciated I have worked on this for the past three days. Thanks

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    A trick or two:

    1) instead of a subform try putting this into the Detail Section of the form and the information in the main form as the Header. The subform can look like a datasheet or continuous form.

    2) to uplift a value from the subfrom to the mainform the value must be a total in the Form Footer sum(SACTotal).

    Simon

  5. #5
    Join Date
    Feb 2009
    Posts
    3

    Subreports

    thanks I will give those a shot and post back

Posting Permissions

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