Solved Trying to sum up a number from a group (1 Viewer)

[rej_co]

Hi

I can’t seem to figure out a summation issue in an Access report that involving two tables in a parent/child relationship. Using a generic example with the following tables:

I created a Parent Only report referencing only the Parent table. The report is first grouped/sorted by Category, then sorted by User. Each User record will display its ValueNum, and those values are summed up for each Category.

Running the report displays the correct information.

The issue is when I try to tie in the data from the Child table to include Additional Info. The query I’m using in that Parent and Child report would be:

SELECT ParentTable.*, ChildTable.*
FROM ParentTable LEFT JOIN ChildTable ON ParentTable.ParentRecID = ChildTable.ParentRecID
ORDER BY ParentTable.ParentRecID, ChildTable.ParentRecID;

The result being:

I created the Parent and Child report using the QueryParentChild query. The report is first grouped/sorted by Category, then sorted by User, then grouped by ParentRecID, and then sorted by Additional Info. I purposely did it this way to show records that have the same information (not including the RecordIDs). I need to have duplicate records not be grouped and show as separate records. Each User record will display its ValueNum, and the intent is to have those values summed up for each Category.

Running the report shows incorrect Total Values.

I understand why it’s doing it. For any ParentTable record that has more than one ChildTable record, the ParentTable information is duplicated in the QueryParentChild query, including ValueNum which is tallied in the summation and inflating the Total Value.

I tried having the Total Value in the report reference the Value text box in the report as opposed to the value in the query, but that doesn’t work.

Maybe it’s a simple fix or maybe I’m going about it all wrong, but I just don’t know how to get around it.

Any assistance would be very much appreciated.

Thanks!

 

[plog]

. I need to have duplicate records not be grouped and show as separate records. Each User record will display its ValueNum, and the intent is to have those values summed up for each Category.

Detail is the lowest level on a report--anything you put in there will show every record. If you put something in a grouping it...groups. Even worse is when you make a grouping on one field and put another field in that grouping. For example, you've got User in both a grouping and the Detail. This is going to give wierd results. Same with Value (which is a poor choice for a name because its a reserved word, instead prefix it with what value it is for).

So, that means Value should probably only appear in the Detail and everywhere else you want to add up those values you use SUM(Value). My real advice though is do one thing at a time. Focus on one field and get it to appear correctly in every section you want. Then move to the next field. You've got a lot of things where they shouldn't be (or not Summed correctly) that you can get lost if you try everything all at once. Focus on one field and get it right then move to getting the next field right.

 

[Pat Hartman]

You are always better off if you make a subreport rather than trying to control the detail presentation using a single recordset for a main report.

 

[rej_co]

You are always better off if you make a subreport rather than trying to control the detail presentation using a single recordset for a main report.

Perfect!! That's what I did and it works now. I've done subforms before but I've never done subreports before. It never dawned on me that I could do that for some reason. Thanks for your help! Much appreciated.

 

[Pat Hartman]

You're welcome.