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!
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!