My running sum didn't work either

I've read all the posts regarding similar problems and consulted the ms
support /kb/208714; all very informative. However, I still can't get it to
work! Any help appreciated. I have a table with fields Index, ItemNumber
and CumQtyCalc. I want to add a field to calculate a running sum by item in
a query based on this table. I can get a running sum for all items by using:

RunningTotalA: DSum("CumQtyCalc","tblTempTable","[Index] = " & [Index] & "")

However, when I try to do by item using the following, it doesn't work:

RunningTotalB: DSum("CumQtyCalc","tblTempTable","[Index] & [ItemNumber] =
'" & [Index] & [ItemNumber] & "'")

Could anyone point out my mistake? Thanks in advance.

8020 wrote:
"I want to add a field to calculate a running sum by item in a query based
on this table"

Couldn't you use a "GroupBy" query to group the records by Index and
ItemNumber and then Sum the CumQtyCalc field?

For instance:
SELECT [Index], [ItemNumber], Sum([CumQtyCalc]) AS SumOfCumQtyCalc
FROM [tblTempTable]
GROUP BY [Index], [ItemNumber];


Hope that helps

8020 wrote:

I've read all the posts regarding similar problems and consulted the ms
support /kb/208714; all very informative. However, I still can't get it to
work! Any help appreciated. I have a table with fields Index, ItemNumber
and CumQtyCalc. I want to add a field to calculate a running sum by item in
a query based on this table. I can get a running sum for all items by using:

RunningTotalA: DSum("CumQtyCalc","tblTempTable","[Index] = " & [Index] & "")

However, when I try to do by item using the following, it doesn't work:

RunningTotalB: DSum("CumQtyCalc","tblTempTable","[Index] & [ItemNumber] =
'" & [Index] & [ItemNumber] & "'")


Try using:

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = '" & Index & "' And ItemNumber = " & ItemNumber)


Note that the quoting in the above assumes that Index is a
Text field and that ItemNumber is a number type field. If
that's not the case, adjust the quotes accordingly.

--
Marsh
MVP [MS Access]

Cheers,

I tried some more but, I'm still having trouble (probably with the quotes?).
I'd appreciate any further assistance. To be more specific, the Index field
is an autonumber; this keeps the records in a specific order and ensures they
are unique, the ItemNumber field is text. The ItemNumber repeats but, always
occurs grouped together due to the Index field. The integers in the
CumQtyCalc are +ve and -ve and I want to have the running sum calculate (for
each record of) each ItemNumber group.

Thanks in advance,



"Marshall Barton" wrote:

8020 wrote:

I've read all the posts regarding similar problems and consulted the ms
support /kb/208714; all very informative. However, I still can't get it to
work! Any help appreciated. I have a table with fields Index, ItemNumber
and CumQtyCalc. I want to add a field to calculate a running sum by item in
a query based on this table. I can get a running sum for all items by using:

RunningTotalA: DSum("CumQtyCalc","tblTempTable","[Index] = " & [Index] & "")

However, when I try to do by item using the following, it doesn't work:

RunningTotalB: DSum("CumQtyCalc","tblTempTable","[Index] & [ItemNumber] =
'" & [Index] & [ItemNumber] & "'")


Try using:

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = '" & Index & "' And ItemNumber = " & ItemNumber)


Note that the quoting in the above assumes that Index is a
Text field and that ItemNumber is a number type field. If
that's not the case, adjust the quotes accordingly.

--
Marsh
MVP [MS Access]

It appears that my guess about the field types was
backwards.

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = " & Index & " And ItemNumber = '" & ItemNumber &
"' ")
--
Marsh
MVP [MS Access]


8020 wrote:
I tried some more but, I'm still having trouble (probably with the quotes?).
I'd appreciate any further assistance. To be more specific, the Index field
is an autonumber; this keeps the records in a specific order and ensures they
are unique, the ItemNumber field is text. The ItemNumber repeats but, always
occurs grouped together due to the Index field. The integers in the
CumQtyCalc are +ve and -ve and I want to have the running sum calculate (for
each record of) each ItemNumber group.


"Marshall Barton" wrote:

8020 wrote:

I've read all the posts regarding similar problems and consulted the ms
support /kb/208714; all very informative. However, I still can't get it to
work! Any help appreciated. I have a table with fields Index, ItemNumber
and CumQtyCalc. I want to add a field to calculate a running sum by item in
a query based on this table. I can get a running sum for all items by using:

RunningTotalA: DSum("CumQtyCalc","tblTempTable","[Index] = " & [Index] & "")

However, when I try to do by item using the following, it doesn't work:

RunningTotalB: DSum("CumQtyCalc","tblTempTable","[Index] & [ItemNumber] =
'" & [Index] & [ItemNumber] & "'")


Try using:

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = '" & Index & "' And ItemNumber = " & ItemNumber)


Note that the quoting in the above assumes that Index is a
Text field and that ItemNumber is a number type field. If
that's not the case, adjust the quotes accordingly.

Works perfectly. Your input has been much appreciated. Thanks Marsh.


"Marshall Barton" wrote:

It appears that my guess about the field types was
backwards.

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = " & Index & " And ItemNumber = '" & ItemNumber &
"' ")
--
Marsh
MVP [MS Access]


8020 wrote:
I tried some more but, I'm still having trouble (probably with the quotes?).
I'd appreciate any further assistance. To be more specific, the Index field
is an autonumber; this keeps the records in a specific order and ensures they
are unique, the ItemNumber field is text. The ItemNumber repeats but, always
occurs grouped together due to the Index field. The integers in the
CumQtyCalc are +ve and -ve and I want to have the running sum calculate (for
each record of) each ItemNumber group.


"Marshall Barton" wrote:

8020 wrote:

I've read all the posts regarding similar problems and consulted the ms
support /kb/208714; all very informative. However, I still can't get it to
work! Any help appreciated. I have a table with fields Index, ItemNumber
and CumQtyCalc. I want to add a field to calculate a running sum by item in
a query based on this table. I can get a running sum for all items by using:

RunningTotalA: DSum("CumQtyCalc","tblTempTable","[Index] = " & [Index] & "")

However, when I try to do by item using the following, it doesn't work:

RunningTotalB: DSum("CumQtyCalc","tblTempTable","[Index] & [ItemNumber] =
'" & [Index] & [ItemNumber] & "'")


Try using:

RunningTotalB: DSum("CumQtyCalc","tblTempTable",
"Index = '" & Index & "' And ItemNumber = " & ItemNumber)


Note that the quoting in the above assumes that Index is a
Text field and that ItemNumber is a number type field. If
that's not the case, adjust the quotes accordingly.