Solved Strange Running Sum Result

[mafhobb]

So I have a simple query to get sales results based on properties and then a grand total:

This is my SQL:

SELECT tblReservations.PropertyName, Sum(tblReservations.ResAmount) AS SumOfResAmount, DSum("ResAmount","tblreservations") AS Expr1
FROM tblReservations
GROUP BY tblReservations.PropertyName
ORDER BY tblReservations.PropertyName;

I expected to get results such as:

Property Amount Running sum
A 5000 5000
B 2500 7500
C 1000 8500
D 2500 11000

Instead I get:

A 5000 11000
B 2500 11000
C 1000 11000
D 2500 11000

What am I doing wrong?
mafhobb

 

[theDBguy]

What am I doing wrong?

Without a criteria argument in your DSum() expression, you are basically just adding up all the records in the table.

 

[mafhobb]

What criteria would I need to enter that would include all the records?

 

[theDBguy]

What criteria would I need to enter that would include all the records?

Well, looking at your sample data, I am guessing you need to add a criteria for the PropertyName.

 

[mafhobb]

Yes, it is grouped by PropertyName, but I do want all properties to show up.

I just wonder what do I need to do to get a running sum as shown at the top. Or is it impossible without criteria? Will it simply add all the values and that's it?

mafhobb

 

[theDBguy]

Yes, it is grouped by PropertyName, but I do want all properties to show up.

I just wonder what do I need to do to get a running sum as shown at the top. Or is it impossible without criteria? Will it simply add all the values and that's it?

mafhobb

Hi. Here's a reference to the DSum() function, in case you need it.

DSum Function [Access 2003 VBA Language Reference]

docs.microsoft.com

Have you tried something like?

DSum("ResAmount","tblReservations","PropertyName='" & [PropertyName] & "'")

 

[Pat Hartman]

In order for the running sum to work, you MUST have a unique identifier for each row. Then the criteria part would be

DSum("ResAmount","tblreservations", "UniqueID <= " & tblReservations.UniqueID) AS Expr1

Using = as the relational operator will give you totals for the UniqueID, not a running sum. To calculate a running sum implies some row order rather than inclusion in a set.

 

[mafhobb]

Hi. Here's a reference to the DSum() function, in case you need it.

DSum Function [Access 2003 VBA Language Reference]

docs.microsoft.com

Have you tried something like?

DSum("ResAmount","tblReservations","PropertyName='" & [PropertyName] & "'")

In this example, I am still required to enter a property name. This would work well if I wanted to know the sum of ResAmount for a specific property, but that is not the case. How can I do this and get an extended total without entering each property as a parameter in the query, but "all"?
mafhobb

 

[Pat Hartman]

Access is not Excel. It works with columns that have names. Please post an example.

 

[mafhobb]

SELECT tblReservations.PropertyName, Sum(tblReservations.ResAmount) AS SumOfResAmount, DSum("ResAmount","tblreservations") AS Expr1
FROM tblReservations
GROUP BY tblReservations.PropertyName
ORDER BY tblReservations.PropertyName;

From this query, all I really need is to extract is a unique DSum numeric value for all properties (one extended sum from all properties added up), so I can use it as rowsource for a textbox

 

[theDBguy]

SELECT tblReservations.PropertyName, Sum(tblReservations.ResAmount) AS SumOfResAmount, DSum("ResAmount","tblreservations") AS Expr1
FROM tblReservations
GROUP BY tblReservations.PropertyName
ORDER BY tblReservations.PropertyName;

From this query, all I really need is to extract is a unique DSum numeric value for all properties (one extended sum from all properties added up), so I can use it as rowsource for a textbox

Hmm, didn't you already try what I gave you earlier in Post #6?

 

[mafhobb]

Hmm, didn't you already try what I gave you earlier in Post #6?

Thanks to your post I found how to get the total for any individual property, but what I need is the sum of all properties.
Am I missing something?
mafhobb

 

[theDBguy]

Thanks to your post I found how to get the total for any individual property, but what I need is the sum of all properties.
Am I missing something?
mafhobb

Isn't the sum of all properties was what you got originally? For example, in your sample data, the sum of all properties is 11000, which is what you said you got.

 

[mafhobb]

OK, yes. But...how do I refer to that total so I can have it as the rowsource in my textbox?

 

[theDBguy]

OK, yes. But...how do I refer to that total so I can have it as the rowsource in my textbox?

Ah, okay. You could actually try this as your Textbox Control Source.

=DSum("ResAmount", "tblReservations")

 

[Pat Hartman]

If you want a value that includes ALL rows, use the folllowing as the ControlSource:

=DSum("ResAmount","tblreservations")

Do not include it in the query. Domain functions run once for each row in the recordset so if the query returns 1000 rows, the DSum() - which is a query will run 1000 times and produce the identical result each time.

 

[mafhobb]

That did it!
Thanks to both of you
mafhobb

 

[theDBguy]

That did it!
Thanks to both of you
mafhobb

Hi. Glad to hear you got it sorted out. @Pat Hartman and I were happy to assist. Good luck with your project.