compute, assign and total values in a query?

[Joe M]

Is it possible to compute, assign and total values in a query?

I'll make the example as easy as possible (my actual application is
different, but if I can see how to do this, I can adapt to my real data).
Suppose I had a table with just one field named "letter", which contained a
single digit letter of the alphabet. In a query I want to examine the value
of the "letter" field and create & compute a new field for the output of the
query and assign it a numerical value (1 to 26 corresponding to the value of
"letter"), and then do a running total on the numerical values I just created.

I'd like to get a query output like this:

letter computed value computed running total
a 1 1
z 26 27
b 2 29
c 3 32

Is this possible (without major difficulty)? Thanks much.

 

[Danny J. Lesandrini]

It can be done, but it's not pretty, and possibly not practical.

Let's assume your query has a logical sort order. One is not shown in
your data below, but let's add a DateTime field of some sort that is specific
to the row, like this ... 02-16-2009 10:23:131 not just 02-16-2009.


SELECT [Letter], [ComputedValue], [ComputeDate]
FROM tblComputedValues ORDER BY [ComputeDate]

You could add a column to your query which calls a Public Function
which you create in a new module. Something like this ...

Add this to the Query ...

RunningTotal: SumCompute([ComputeDate])

Public Function SumCompute(ByVal dteCompute As Date) As Long
' Assumes dteCompute is a DateTime value that identifies the exact comput time.
Dim sSQL As String
Dim lResult As Long

strSQL = "SELECT [ComputedValue] FROM tblComputedValues ORDER BY [ComputeDate]"
lResult = NZ(DSum("ComputedValue",strSQL," [ComputeDate]>=#" & [ComputeDate] & "#"), 0)
SumCompute = lResult
End Function

The query will be re-executed in this function for every row, so if your query will have
thousands of rows, this will completely bog down the system, but it will work.

 

[John Spencer]

It is fairly easy to return a running sum in a report.

Running sums are much more difficult in a query. Since you have to use a
subquery to get specific sets of records for each record and that means
you need some method for identifying the records that would come before
the current record. The three fields you show in your example are
insufficient to do that UNLESS letter is unique (a to z only one time)
and you want the running sum in the order of the letters a to z.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

Sure. It ca be as 'simple' as:

SELECT a.letter, LAST(a.computedValue), SUM(b.computedValue)
FROM tableName AS a INNER JOIN tableName AS b
ON b.letter <= a.letter
GROUP BY a.letter



That assumes there is no duplicated value "letter". Note that the same
source of data, tableName, is used twice with different alias. Consider this
as if you have one single list, the table, and you use two of your fingers,
fingerA and fingerB, to point data of that list. FingerA runs over all the
possible values (GROUP BY a.letter), while fingerB is limited to the
condition (ON b.letter <= a.letter), and for such arrangement of all
possible records that can reach fingerB, you compute SUM(b.computedValue).
As far a a.ComputedValue, that is mostly from a requirement of a total
query. You could have used:


SELECT a.letter, a.computedValue, SUM(b.computedValue)
FROM tableName AS a INNER JOIN tableName AS b
ON b.letter <= a.letter
GROUP BY a.letter, a.computedValue


but that could be a little bit slower, although not perceptible.




Vanderghast, Access MVP

 

[Danny J. Lesandrini]

Agreed, and you can easily output the report to Excel, which would give you
a nice looking datasheet with the running totals in their own column.