By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,122 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

SUM Query results on report

P: 74
I have a report, and on the report is a min, avg, and max query each with (ok who really cares) 20 or so records. At the footer of the report i would like to have a total. I requery for each record, and now i am copletely lost at how to add temporary (query) values, once they are finished. Please Help!! Seriously, i will be forever grateful.

I am assuming that i have provided enough information. If I havent, please let me know.

***Edit***
Ok, this might be important. I am querying for information in a listbox. Only the first value matters (since i am using min avg and max, i am only getting one result.... but you knew that) but it is not selected (ok, you knew that too).
Mar 9 '09 #1
Share this Question
Share on Google+
4 Replies


maxamis4
Expert 100+
P: 295
I have found the best way to do this is to use the Sorting and Grouping properties of the report. Create a custom header and footer using the Sorting Grouping properties. All you have to do is under group properties is set the value to Yes on both Group header and Group footer.

To get to this right click on the top left corner of the report where you see the black square. go to Sorting and Grouping properties. Then use a text box to sum, count or average any value you want. Much cleaner than a footer if you have hundreds of records.
Mar 9 '09 #2

P: 74
OK, so i did that, i recreated the query.... saved it.... but when i group by expression and choose query....it doesnt work. When i choose sum, it wont let me choose the query name, it only lets me choose the fields that are on the table.
Mar 10 '09 #3

P: 74
Nobody can help?

How about this approach then. Is there a way that i can get copy the value from the listbox, into the type of box that i CAN work with. i tried =(list47) in a textbox but that didnt work, but if i could get them in another type of box then i could atleast sum the data.

Another thing i tried was.....
After i wrote the query and saved the query. It looked like this

qryAVG
SELECT Avg(tblPODetails.unitprice) AS AvgOfunitprice
FROM tblPODetails
GROUP BY tblPODetails.pn
HAVING (((tblPODetails.pn)=[reports]![rptbomcost].[pndetails]))
ORDER BY Avg(tblPODetails.unitprice);

I then tried to do a sum query in the footer that looked like this

qryAVG Query
SELECT DISTINCTROW Sum([qryAvg].[AvgOfunitprice]) AS [Sum Of AvgOfunitprice]
FROM qryAvg;

I dont understand if i took the wrong approach with this, but I do know that it isnt working. I tried requery in the format of the footer, but It only displayed the result from the first record (no requery) or the last record (requery). If this wont work, i can scrap the report..... but i know that it should be able to work.
Mar 10 '09 #4

P: 74
Figured out a method.....
Mar 11 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.