Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Report totals problem

    Hi,
    I've not used Access for about 3 years and so am a little rusty but I'm having a problem with a really simple report.

    The report is fed by a query that links 3 tables. Within the report I want to group and sort various fields. The grouping is done at 2 levels. There is in addition a parameter selection in the feed query that only selects certain records, these being a field in a form in the format [Forms]![Policies_frm]![RiPolicyRef]
    I'm trying to have a sum total appear in the highest level of grouping [RiPolicyRef] by using a text box and the =sum([***] where *** is the field name I want to sum on and placed in the [RiPolicyRef] footer.
    Unfortunately the the sum totals are giving me the totals for all records in the table and not just those that are included in the parameter selection. I have running sum set to "No"

    I'm obviously missing something but for the life of me I can not see what the problem is.

    Your help would be most appreciated

    Regards
    John

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    so, you have parameter query that gives you a filtered rowset and a report based on this query, than you say that by agregate sum function you get sum of all records, not filetered ones, NO WAY, probably, your parameter doesn't filter query and you have all data (not filtered ) shown in the report, check this - do you see only the supposed records in the report or all records from the tables?
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    Yes I'm seeing only the records I should be seeing. I've double checked this as the table as over 4,000 items but the report is only showing me the 56 record sub-set that I was expecting to see. The total for these should be around 900/- but the report is giving me 3.5m.

    But I think that you maybe on the right lines as if I use another report that does not use the parameter query, then the total is correct for that group of records. Also I noticed that the 3.5m is not in fact the total of all records, but as yet I'm not certain what is making this total up.

    Also I notice that if I copy the required field from the report that gives the correct total and paste that into the incorrect one, I still get the wrong answer. I think I need to ascertain why I am getting the correct number of records but the wrong totals and how this total is made up. Funnily enough if I put a count in the footer, it gives me the correct count of records (56)

    Regards
    John

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Ok I can see what the problem is now, its because the query that feeds this report is drawing its data from 3 tables and the totals I require are form the master table, obviouslyit is summing up this field for every sub record not the matser records.

    Not sure how I get around this?

    Regards
    John
    Last edited by Sticker; 02-23-04 at 11:24.

  5. #5
    Join Date
    Feb 2004
    Posts
    199
    I know MS Access very vell but can't figure out why you have problem, try this
    figure out on which query the report is based, run this query and calculate totals (you can use excel to do this). first of all you should be shure that Query gives correct lines.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    also total amount depended where you put it, if you put it in details , it won't give an effect , if you put it in group , it'll sumarizes over groups, if you put it in report footer, it sumarizes among whole records
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Originally posted by Kakha
    also total amount depended where you put it, if you put it in details , it won't give an effect , if you put it in group , it'll sumarizes over groups, if you put it in report footer, it sumarizes among whole records
    Hi,
    I've gotten around this by writing a sum query on just the master records and then joined this to the original query. The fields from the summed query are then put into the footer record. I probably didn't explain very well what I had found in my last post. Basically for each sub record there was a colum of data from the master record and I was putting this field in the footer for the master record, therefore for each master record if there was 2 sub records I would have double the amount in the footer, but because the number of sub records to each master record was variable, the summed total was not an exact double, triple etc. By extracting the sum before any totalling was done in the footer record, I have avoided this doubling,tripling effect.
    Probably not the correct way to do it

    Thanks for your help.

    Regards
    John

  8. #8
    Join Date
    Feb 2004
    Posts
    199
    sorry, can't get what are u talking about
    If your app is less than 1 mb in size, send it to me (I'll post you my e-mail via private msg)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  9. #9
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Kakha,
    Thanks for the offer, unfortunately the app is around 8.5Mb in size.

    I'll try to put in context a simplified versionof what happened

    Table A links to Table B
    Table A links to table C

    Table A is the master record, Table B is the sub record and for every record in table A there is a minimum of 1 record in table B, but mostly there is 2 to 4 records in table B for every related record in Table A.

    The field PolicyRef in table A is the first group header, the second header is also in table A, this is called Collnote. Table B is in the details section of the report. The second header (Collnote) has not associated footer recorder. The 1st Header has an associated footer record that contains the fields that I want to be summed (****).

    The query that feeds this report was originally structured as follows

    Table A joins to table B and table A joins to table C. The output of the query is restricted to only those records where the field PolicyRef in table A matches that displayed in the form with the same field name.

    Therefore the query was outputing all the associated records form table B and certain fields form table A. As follows is an example;

    PolicyRef(Table A), Security (Table B), YOA(Table B), USDREQ (table A).
    This produces 56 rows of data, which is in the detail section of the report. But in Footer 1 I want the sum of USDREQ for each record that is has the same PolicyRef. I therefore only want to sum the 22 records that exist in table A not table B. This field was put in footer 1 and summed. But there was 56 examples of this and it should be 22.

    Sorry that as clear as I can make it, apologies for the "foggyness" of my posts.

    Your help is really appreciated.

    Regards
    John

  10. #10
    Join Date
    Feb 2004
    Posts
    199
    At last I can se your problem
    so you have a query with rowset of master-child tables' rows, and you need to get sum of field valuse that is in master table, but since there is a child table's rows too , you get another amount, why? answer is easy, Access sums data as excell on table, regardless report structure, if you look in your query's results, you can see that the master table's USDREQ values is shown many times and you get sum all of them.

    There is a way out:
    put a textbox (suppose with name Text1) in TableA-s group section header with bound to USDREQ.
    (hide it if you don't need it in this section)
    set RUNNING SUM - Over All (or Over Group if you have an upper section)
    then put a textbox in Footer 1 and set source =Text1

    that's all
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  11. #11
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Thumbs up

    Cheers mate

Posting Permissions

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