Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    37

    Unanswered: Sum fields in report

    Hi All,

    I asked a question last week, and maybe was a little confusing, so trying again. Two of the fields in the table that categorize data are divisions and purchasetype. Each purchase has both a division category and a purchasetype category, as well as an amount. I'm creating a report and would like to have a text box that has the total of each combination of divisions/purchasetypes, i.e., divisionA/purchasetypeB, divisionC/purchasetypeM, etc. Each text box would have the total for a certain division/purchasetype combination. Also, I would like to filter the record for a certain time period (each record has date field). How can I do this?

    Thanks,
    Bruce

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its doable
    first thing to do is to get the correct data (IE a query)
    then set appropriate sorting with in the report
    set appropriate headers and footers.

    does your report need to show all purchasetypes irrespective of whether anythign has been spent this period

    for know don't worry about the date filter, leave that for later.

    so the first step is the extract query that gets the rows you required from the underlying tables. its kinda tought to give any pointers as we don't know your table structure
    and Im not entirely clear what it is you want to do.

    im guessing its sort of


    Code:
    Division: X (god knows)
    Type A: 1,500.00
    Type B: 4,234.24
    Total:  5,734.24
    
    Division: Y (why indeed)
    Type A: 5,000.00
    Type C: 1,234.56
    Total:  6,234.56
    
    Analysis by Type across all divisions
    Type: A 6,500.00
    Type B: 4,234.24
    Type C: 1,234.56
    Total.....
    the analysis across all division may require a but of VBA in the background, or use a sub report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2005
    Posts
    37
    Healdem,

    What I'm trying to accomplish is to have a report that resembles a spreadsheet. Divisions will be columns and purchasetype will be in rows. Fields would be set up in detail section.

    The field for Division X Type A (your example above) would be the total of all purchases with Division X Type A. Division X Type B would be a separate field. I though about creating a separate query for each division/type combination and using the total amount in each query as the source for each field. I'm not sure how I would bring those into the report because I thought the report has to obtain all of the records from the same recordsource (that is correct, isn't it?). I also thought about making a query that would capture the totals from each combination, but haven't figure out how to do that yet. Any advice?

    Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry a piccy is worth a thousand words
    if you wanrt a spreadsheet type dispaly then you need to use a pivot table.

    but intrinsically its no different you still need to marshall your data first
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2005
    Posts
    37
    The pivot table helps, but doesn't do what I'm trying to accomplish. I think I'm going to create a query for each combination, then creating another query that will pull all of the data in. That query will capture the amount from each of the other queries (divisionA/typeB, divisionA/TypeC, divisionB/TypeA, etc). That way, I can set the recordsource in the report to that query and tie the appropriate field in the query to the field in the report. Problem I'm running into is some queries have one entry and others have many more. What ends up happening is the query with one entry repeats for the maximum amount of rows - based on the record with the largest amout of rows. When I sum the field for one entry, it adds the duplicates. How can I eliminate the duplicates? If I can figure this out, I'll be able to complete my report.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of seeing your data design then not sure I can help. what I'd suggest you do is consider stripping out any sensitive daat and post the db here
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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