My running sum didn't work either

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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] & "'")
Click to expand...


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.
 
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 said:
8020 said:
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] & "'")
Click to expand...


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.
Click to expand...
 
It appears that my guess about the field types was
backwards.

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

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 said:
8020 said:
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] & "'")
Click to expand...


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.
Click to expand...
Click to expand...
 
Works perfectly. Your input has been much appreciated. Thanks Marsh.


Marshall Barton said:
It appears that my guess about the field types was
backwards.

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

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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top