Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Unanswered: Using a Calclated Total From a Form in a Report

    I have a NewHouseMainF form that calculates orders for Builder/Subdivision/House Type. Each Builder/Subdivision has several House Types. I need a proposal form that will break down each Builder/Subdivision and show only a total figure for every House Type. (I also have a NewHouseFromR2 report that performs the same calculations as the NewHouseMainF.)



    Each item needed in the order is made through a subform. Each item falls into one of three catagories, such as Material, Labor and Subcontract. Each of these items have been assigned a catgory and my calculations, break these items into the three sums using Iff statements. Then other calculations are performed on these sums, such as taxed amount and markup. I then want to do the proposal form and I don't want the figures that went into the calculation to be on this report. I only need the grand total amount for each Builder/Subdivision/House Type.



    I have made the RoofProposalR report and made an attempt at entering =Forms!NewHouseMainF!GrandTotal in the control source of the field “Roof Price” but it returns #Name? Is there anyway to get the Grand Total figure from either the NewHouseMainF form or the NewHouseFromR2 report? Please say there is a "simple" solution!

  2. #2
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Forms![Formname]![Control] is correct - I have a feeling that your problem lies in the fact that the form you're referencing is not open. Try opening the form and leaving it open and then open the report to see if the #Name problem is resolved. If it is, just use a bit of code to open the form prior to the report and hide it until the report is closed. It might not be the most elegant approach, but it should work.

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    Smacdonaldnc, you were exactly right. However now that it worked, it isn’t the results I was looking for. Right now it is putting the Grand Total of the open form in every House Type Grand Total.



    The way my data is set up is that there is one Builder/Subdivision that has several House Types. What I am looking for is a report that will list Builder/Subdivision and all of the grand totals for each house type. For instance:



    Builder: Freedom Builders

    Subdivision: Oakridge

    House Type Total

    A01 $2,000.00

    B01 $1,500.00

    C01 $2,500.00



    There is a separate NewHouseMainF for every Builder/Subdivision/HouseType. Is there another way that I can get the result above? I am a novice at Access and I know very very limited code. I have attached my database in the hopes that someone will be able to help me!

    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Anne,

    Take a look at the modified version of your database in the attachment below. I made 3 reports that I think are what you were trying to achieve, but not totally certain. Anyway, in the DatabaseWindow look to the right and there is a bar that says Groups Look there and you will see one group called BudsReports There you will have easy access to the reports that I created. Like I said that might not be what you were seeking but as best I can tell this time of morning that is it. If not come back and someone else may be able to assist you more if I can't.

    have a nice one,
    BUD
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2004
    Posts
    8
    Thank you Bud, but that isn’t quite what I’m looking for. The thing that is giving me the biggest headache is that my material list is broken into three categories, Material, which gets taxed, Labor which has a markup and then the Subcontract fee which gets added to the other three. I have done this with IIf statements on the footer of the subform. Ex.



    Category –Material-Qty-Price-Total

    Material - 20 Yr – 1 - $ 20.00- $20.00

    Material – Caps – 2 - $10.00- $10.00

    Subcontract-Bay W -1 - $15.00 -$15.00

    Labor – SORV – 2 – $5.00 - $ 10.00



    Material

    =Sum(IIf([CategoryId]=1,[TotalSum],0)) which yields $30.00

    Subcontract

    =Sum(IIf([CategoryId]=2,[TotalSum],0)) )) which yields $15.00

    Labor

    =Sum(IIf([CategoryId]=3,[TotalSum],0)) )) which yields $10.00



    Then I reference these three amounts in the footer of the NewHouseMainF and perform the following calculations:



    MaterialTotal

    Material*1.045)) which yields $31.35

    LaborTotal

    Labor*1.25 )) which yields $18.75

    Markup

    =sum MaterialTaxed+LaborTotal)) which yields $50.01

    MarkupTotal

    Markup*1.32)) which yields $66.13

    GrandTotal

    =Sum MarkupTotal+Subcontract



    This is the GrandTotal amount that I want in my Proposal form for every Builder/Subdivision/HouseType. I have a NewHouseFromR report that performs the same calculations. Is there Anyway to get the GrandTotal amount from either the form or the report on the proposal report in the design that I wrote in my first post?



    I know this is confusing and I appreciate all of the help and patience because I am totally baffled.



    Anne

Posting Permissions

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