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] & "'")

 

[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

 

[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")

 

[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.