Sum Header Row - Not Details

[jre1229]

I have a report that is grouped by a field. The header row only displays the first record. I am trying to sum up the numbers in the header row, but when I add a sum to the field it sums up all of the records, similar to what it would do if I had it in the detail section. For instance, it is grouped by a part number, the part number has a field I am trying to sum up, but this number may appear 3 times if I included it in the details section, but I only want it to count once. Any suggestions, my brain is fried as I feel like I've tried everything.

[June7]

Don't understand what you want. Sum is an aggregate function - it belongs in group or report header/footer. If you want to add fields, then in query or textbox use an expression: [field1] + [field2]

[jre1229]

So to sum what is in a header field, do you have to use an expression?

[June7]

Do you want to add fields together or do you want to sum records (the data in a single field)?

[jre1229]

I want to sum data in a single field. The problem is when I place the field I want to sum in the section header I get one record for each "Part Number", which is a separate field but it is the field I am grouping by, when I place the field in the details section, I will get 3 or 4 records of the same part number, which also just duplicated the field I am trying to sum. So when I sum my field, it sums up all 3 or 4 records, but I only want to sum what is in the header with what is in the header row of the next group, or "Part Number" in this instance. Let me try to give an example below

Example Date:
Part Number / Location / TotalQtyOnHand
2424 / Home / 3
2424 / Shed / 3
2424 / Other / 3
2473 / Home / 6
2473 / Other / 6

I have a group based on Part Number which if I put the details in the header, I would end up with one line showing 2424 / Home / 3 and one showing 2473 / Home / 6 . But if I sum the TotalQtyOnHand, it will come up with 21 which would be a total of all records, but I need to come up with 9, which would be a total of just what is in the header section.

[June7]

Aggregate calcs work in the detail data. If you get 'duplicates' then the report RecordSource must be including several tables with a many relationship to the primary. You might need a subreport.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify report involved.

[jre1229]

Unfortunately the query is linked to a backend that I can not share. Below is the SQL code, don't know if that helps or not but yes it includes several tables.

SELECT VENDOR.NAME, VENDORPARTS.LEADTIME, [BOM Relationships]![Child Item] AS [Child Num], [BOM Relationships].[Child Descr], Round((([Demand Build UP 12 27]![Qty in FG]+[BOM Relationships]![Child Qty OH]-[BOM Relationships]![Child Alloc]+[BOM Relationships]![Child PO])/([Demand Build UP 12 27]![AvgOfChild Avg]+0.001))*52/12,1) AS [Total Wks OH], [BOM Relationships].[Child Qty OH], [BOM Relationships].[Child PO], Round((([BOM Relationships]![Child Avg])*12/52),1) AS [Avg Wk], [BOM Relationships]![Child Qty] AS [Child Qty], [BOM Relationships]![Parent Num] AS [L1 Num], [BOM Relationships]![Parent Alloc] AS PA, [BOM Relationships]![Parent Qty] AS [Qty L1], [BOM Relationships].[Parent Qty], Round(([BOM Relationships]![Avg Mth]*12/52),1) AS [Avg Wk FG1], Round(((([BOM Relationships]![Parent Qty]-[BOM Relationships]![Parent Alloc])/([BOM Relationships]![Avg Mth]+0.0001))*52/12),1) AS [Wks OH FG1], [BOM Relationships_1].[Parent Num], [BOM Relationships_1].[Parent Qty], [BOM Relationships_1].[Parent Alloc], Round(([BOM Relationships_1]![Avg Mth]*12/52),1) AS [Avg Wk FG2], Round((([BOM Relationships_1]![Parent Qty]/([BOM Relationships_1]![Avg Mth]+0.0001))*52/12),1) AS [Wks OH FG2], [BOM Relationships_1]![Parent Qty] AS [L2 Qty OH], [PO Min 12 27].POQ, [PO Mul 12 27].POQ, PART.ACTIVEFLAG, VENDORPARTS.DEFAULTFLAG, Round(([Demand Build UP 12 27]![Qty in FG]/[Demand Build UP 12 27]![CountOfChild Item]),1) AS DB, Round(((([Avg Wk FG1]*[Lead Time])-[Qty L1]))*[Child Qty]) AS [QTY Need], [Enter Week Multiplier] AS [Lead Time], Round([Qty L1]*[Child Qty]) AS DB2, Round(([BOM Relationships]![Child PO]+[BOM Relationships]![Child Qty OH])/(([BOM Relationships]![Child Avg])*12/52)) AS [WEEKS ON HAND], [Forecast Averages].WeeklyAverage
FROM ((([Demand Build UP 12 27] INNER JOIN (((([BOM Relationships] LEFT JOIN [BOM Relationships] AS [BOM Relationships_1] ON [BOM Relationships].[Parent Num] = [BOM Relationships_1].[Child Item]) INNER JOIN PART ON [BOM Relationships].[Child Item] = PART.NUM) INNER JOIN VENDORPARTS ON PART.ID = VENDORPARTS.PARTID) INNER JOIN VENDOR ON VENDORPARTS.VENDORID = VENDOR.ID) ON [Demand Build UP 12 27].[Child Num] = PART.NUM) LEFT JOIN [PO Min 12 27] ON [Demand Build UP 12 27].[Child Num] = [PO Min 12 27].NUM) LEFT JOIN [PO Mul 12 27] ON [Demand Build UP 12 27].[Child Num] = [PO Mul 12 27].NUM) INNER JOIN [Forecast Averages] ON PART.NUM = [Forecast Averages].Num
WHERE (((VENDOR.NAME) Like [Vendor - Ex Ride*]) AND ((PART.ACTIVEFLAG)=1) AND ((VENDORPARTS.DEFAULTFLAG)=1))
ORDER BY [BOM Relationships]![Child Item], [BOM Relationships].[Avg Mth] DESC;

[June7]

Doesn't help. Only you know table relationships. If there are several 'many' tables in the query, you will get the 'duplicates'. Use subreports instead.

[jre1229]

Let me ask you this as I believe it could be a work around that may work just fine. I added a text box to my report and set the control source equal to the field in the header that I need to sum. Now all I need to do is sum up the text box. The text box name is Text27. I tried =sum([Text27]) but it doesn't know what Text27 is, is there another way I need to refer to a text box that I manually added?

[June7]

SQL aggregate functions must reference fields in the report RecordSource, not controls.

You could try a domain aggregate function (DSum) expression in textbox. The DSum would reference the table instead of the report RecordSource.