HELP with DSUM

[Guest]

Hello,
Using ACCESS97
Trying to use DSUM for running totals in a query. I’m not very good with
SQL. I use the query design sheet. Read MS support article id 138911 on
this, but I get the same amount for my ‘runsum’ field. Other than the
RUNSUM, the only other field listed below that is numeric is the PAID field.

Field: CO Table: UI H Total: Group By Show: Yes Criteria: “PIâ€
Field: CARRIER Table: UI H Total: Group By Show: Yes
Field: PAID Table: UI H Total: SUM Show: Yes Criteria: <-4999.99
Field: Count: Count(*) Total: Expression Show: Yes
Field: RUNSUM: DSum("PAID","UI H") Total: Expression Show:YES
Field: CATEGORY Table: UI H Total: Group By Show: NO Criteria:
“RECOUPâ€
Field: STATUS Table: UI H Total: Group By Show: NO Criteria: <>â€Xâ€
Field: P-FLG Table: UI H Total: Group By Show: NO Criteria: Is Null


Any help on using this function would be helpful to future projects.

 

[John Vinson]

Hello,
Using ACCESS97
Trying to use DSUM for running totals in a query. I’m not very good with
SQL. I use the query design sheet.

You're using the query design sheet - as a tool to create a SQL query,
which can be posted to a newsgroup. We'll understand it much better
than the gemisch below. Please open the query in SQL view (use the
View menu or the leftmost toolbar icon dropdown) and post it here.

Read MS support article id 138911 on
this, but I get the same amount for my ‘runsum’ field. Other than the
RUNSUM, the only other field listed below that is numeric is the PAID field.

Field: CO Table: UI H Total: Group By Show: Yes Criteria: “PI”
Field: CARRIER Table: UI H Total: Group By Show: Yes
Field: PAID Table: UI H Total: SUM Show: Yes Criteria: <-4999.99
Field: Count: Count(*) Total: Expression Show: Yes
Field: RUNSUM: DSum("PAID","UI H") Total: Expression Show:YES
Field: CATEGORY Table: UI H Total: Group By Show: NO Criteria:
“RECOUP”
Field: STATUS Table: UI H Total: Group By Show: NO Criteria: <>”X”
Field: P-FLG Table: UI H Total: Group By Show: NO Criteria: Is Null

Well... DSum("Paid", "UI H") does not calculate a running sum, it
calculates the sum of all records in the table, without regard to any
criteria or other considerations from this query. Do you have some
field in the table which is different for each record, and can be
sorted into ascending order for your "running"? If so, you'll need a
third argument to DSum() such as

DSum("Paid", "UI H", "[ID] <= " & [ID] & " AND [P-FLG] IS NULL AND
STATUS <> 'X' AND CATEGORY = 'RECOUP'"

i.e. using the same criteria as needed to retrieve the records that
you're trying to sum, with an additional criterion that you only want
to sum the records running up to the current record.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Thank you for your response. I changed my DSUM formula per your instructions
to:

RUNSUM: DSum("PAID","UI H",[CARRIER]<=" & [CARRIER] & " And [P-FLG] Is Null
And [STATUS]<>'X' And [CATEGORY]='RECOUP')

where the [CARRIER] field is different, however, with this formula the
RUNSUM is now blank. I guess I'm having a hard time understanding how Access
does this. Could you provide any additional insight to this just one more
time? The CARRIER field was a text field if that matters.

Hello,
Using ACCESS97
Trying to use DSUM for running totals in a query. I’m not very good with
SQL. I use the query design sheet.

You're using the query design sheet - as a tool to create a SQL query,
which can be posted to a newsgroup. We'll understand it much better
than the gemisch below. Please open the query in SQL view (use the
View menu or the leftmost toolbar icon dropdown) and post it here.

Read MS support article id 138911 on
this, but I get the same amount for my ‘runsum’ field. Other than the
RUNSUM, the only other field listed below that is numeric is the PAID field.

Field: CO Table: UI H Total: Group By Show: Yes Criteria: “PIâ€
Field: CARRIER Table: UI H Total: Group By Show: Yes
Field: PAID Table: UI H Total: SUM Show: Yes Criteria: <-4999.99
Field: Count: Count(*) Total: Expression Show: Yes
Field: RUNSUM: DSum("PAID","UI H") Total: Expression Show:YES
Field: CATEGORY Table: UI H Total: Group By Show: NO Criteria:
“RECOUPâ€
Field: STATUS Table: UI H Total: Group By Show: NO Criteria: <>â€Xâ€
Field: P-FLG Table: UI H Total: Group By Show: NO Criteria: Is Null

Well... DSum("Paid", "UI H") does not calculate a running sum, it
calculates the sum of all records in the table, without regard to any
criteria or other considerations from this query. Do you have some
field in the table which is different for each record, and can be
sorted into ascending order for your "running"? If so, you'll need a
third argument to DSum() such as

DSum("Paid", "UI H", "[ID] <= " & [ID] & " AND [P-FLG] IS NULL AND
STATUS <> 'X' AND CATEGORY = 'RECOUP'"

i.e. using the same criteria as needed to retrieve the records that
you're trying to sum, with an additional criterion that you only want
to sum the records running up to the current record.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[John Vinson]

Thank you for your response. I changed my DSUM formula per your instructions
to:

RUNSUM: DSum("PAID","UI H",[CARRIER]<=" & [CARRIER] & " And [P-FLG] Is Null
And [STATUS]<>'X' And [CATEGORY]='RECOUP')

where the [CARRIER] field is different, however, with this formula the
RUNSUM is now blank. I guess I'm having a hard time understanding how Access
does this. Could you provide any additional insight to this just one more
time? The CARRIER field was a text field if that matters.

It does matter; text field criteria must be delimited with either ' or
", using the latter if the text field might contain an apostrophe. To
get a " in a string delimited by ", you can either use two consecutive
doublequotes, or concatenate the character using its ASCII value as I
do below. The third argument to DSum must be a TEXT STRING - you're
missing some quote marks.

Try

RUNSUM: DSum("PAID", "UI H", "[CARRIER]<=" Chr(34) & [CARRIER] &
Chr(34) & " And [P-FLG] Is Null And [STATUS]<>'X' And
[CATEGORY]='RECOUP'")

This will pass a search criterion (assuming that Carrier is Fred's
Trucking) like

[Carrier] <= "Fred's Trucking" AND [P-FLG] IS NULL AND [STATUS] <> 'X'
AND [CATEGORY] = 'RECOUP'

which will get all values of CARRIER which are alphabetically before
FRED...

John W. Vinson[MVP]