If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Getting a Total of all Values (Not Just Top 25)

I've set up a report that pulls the top 25 values of a field([ Total]) from a query ([Supplier]). It's easy enough to get the sum of the 25 values. Is there a way to put the sum of all the values(not just the top 25) on the same report?

ummm, he's only pulling the top 25, that's the whole point of the post.

You have a couple options here. If the query is simple and based on a single table, you could use the DSum() function in an unbound textbox. An example of that would be:

=DSum("yourField", "yourTable", "somecriteria = something")

If the query is based on a more complex query, you could use a subreport and place the "select sum()" statement there. Alternately, you could use a subquery, and attach the number to each record, that method is a little bit of a memory hog, but it will work just dandy if you aren't too concerned with optimal performance. That might look like this:

SELECT TOP 25 yourField, yourOtherField, (SELECT Sum(YourField) FROM yourTable WHERE somestuff) AS yourTotal
FROM yourTable
WHERE some stuff