Summation of Fields in a Table based on Query Results (1 Viewer)

[jdunca4]

I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use. I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value. Any help would be appreciated. If further information is needed please let me know.

 

[Cronk]

If you only want the sum to appear once instead of for every record, put the sum in the footer.

 

[jdunca4]

Some of the reports are long so I was hoping to keep it in the header.

 

[jdunca4]

PS summing in the footer still provides incorrect data. When the query runs the field I want to sum is associated with each line item thus the sum is each line item when I am looking for the sum of the unique line items. i.e. WRNumber is assoicated with many different points and each point has 39 questions. Thus when the query runs for questions that were answered "No" it will return each WRNumber that has a point that has a question with an answer of "No". Now the WRNumber might be the same for say 10 points and those 10 points might have 2 questions each thus giving me a summation of 20 when in reality I am looking for the answer to be 1 because there is only one WR number it just so happens that it has 10 points each with 2 questions that equal "No". Make sense

 

[Cronk]

If you want the overall sum of the values of all of the detail lines, then add a hidden text box in the Detail section, set equal to the text box containing the line value. Set this text box to be Running Total. In the footer, add a text box equal to the hidden text box.

If you want the overall sum in the header (before the detail data is formatted), you will need to have use some code in the Header format event.

 

[jdunca4]

I want the sum of the unique values in each column of the query results. Each row in the query is unique but each column may contain the same data over and over again which is causing my summation to be more than it needs to be. If I could count unique values in each column of the query results and have that value show up in the header of the report that is what I am aiming for. I do appreciate your help.

 

[Pat Hartman]

You probably need to separate the query into two and create a main report with a sub report. That will allow you to sum the data correctly. You don't tell us what your application is but an example that comes to mind is Orders. An Order will have a shipping amount and a tax amount. If you made a query of Order plus Order details, the shipping and tax amounts would "duplicate" and you couldn't sum them but by separating into a report (for order) and sub report (for order details), you can sum both sets of data properly.

 

[Cronk]

Pat

We both do not know the data structure of the OP. But with your example I'd be using the footer of the Order group. No?

 

[Pat Hartman]

You would use the footer of the order group to sum the details. You would use the report footer to sum the orders.

 

[jdunca4]

My query is set up such that the footer won't work as it sums all the rows instead of the distinct values in the particular column. I had to right a function to make it work. As I am a novice in Access I am pretty sure why mine isn't working the way folks are suggesting is due to the way I initially built the query the report is deriving its information from. The function I built and then referenced in a text box on the report is:
Function WRCount()
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "SELECT DISTINCT([Design Answers Query].WRNumber) AS Expr1 FROM [Design Answers Query];"

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then
WRCount = 0
Else
rs.MoveLast
WRCount = rs.RecordCount
End If
End Function

 

[Cronk]

Are you saying you do not want the sum of the values in each row, but rather the count of rows?

 

[jdunca4]

I want the count of unique values in a specific column of the query results. (i.e. Column A going down has cow, cat, cat, cat, dog, dog, fish, fish, fish, fish) Summing in the footer returns a value of 10 when I want the count of the unique values which would be 4)

 

[Pat Hartman]

Add a group for column A. Then in the footer for column A, you can add a control to count the rows.

=Count(*)

 

[Cronk]

jdunca4, I would use a subreport with the recordsource being based on a query grouped on Animal.