DSum Function in MS Access query

Hi

I am having some trouble creating a DSum function in a MS Access query.  The function is supposed to:

sum Actual Sales
in tblRawDataSub
where RawDataMain ID = RawDataMainID AND MonthID <= MonthID

The RawDataMainID refers to a particular market and product.

Here is the code that I have:
RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID = '" & [RawDataMainID] & "' and tblMonths.MonthID <= " & [tblMonths.MonthID])

Can someone please tell me where I am going wrong?

 Screenshot from MS Access query
maliqsmomAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

use this query


SELECT Q.*, (select sum([actual sales]) from query1 Q2 where Q2.[RawDataMainID]=Q.[RawDataMainID] and Q2.[MonthID] <= Q.[MonthID]) AS RunningSum
FROM Query1 AS Q;


run query2 from the db
Database14.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Try it like this perhaps:

RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID =" & "'" & [RawDataMainID] & "'" & " and tblMonths.MonthID <= " & [tblMonths.MonthID])

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID = '" & [RawDataMainID] & "' and tblMonths.MonthID <= " & [tblMonths.MonthID])>>

<<Can someone please tell me where I am going wrong?>>

 Well for starters, using a DSum() in a query<g> .   Any of the domain functions should not be used in a query.  They are totally unoptimizable by the query parser and will give you very poor performance.

  So what do you do?  Well all the Domain functions are nothing more then executing a SQL statement, which means you can write a query to replace it.

  Open a new query and add your sales table.  Change the type to a GROUP BY.  Now pull down into the grid in the order you want all the records grouped on (main ID and month).  Pull down the sales amt and in the total line, change it to sum.  Try executing the query and make sure it gives you the totals you want.

Save it.

Now open the first query and add the saved query as a "table".

Add joins to your existing table (ie. on Main ID).

Then pull the sum() field down into the query grid.

and it will be a heck of a lot faster too!

Jim.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jeffrey CoachmanMIS LiasonCommented:
But...[RawDataMainID] looks numeric in your sceenshot, so try:

RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID =" & [RawDataMainID] & " and tblMonths.MonthID <= " & [tblMonths.MonthID])
0
 
Rey Obrero (Capricorn1)Commented:
try this


RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID =" & [RawDataMainID] & " and [MonthID] <= " & [tblMonths.MonthID])


what are the entries in table "tblMonths" ?

0
 
Rey Obrero (Capricorn1)Commented:
try this, wrong bracketing


RunningSum: DSum("Actual Sales","tblRawDataSub","RawDataMainID =" & [RawDataMainID] & " and [MonthID] <= " & [tblMonths].[MonthID])


what are the entries in table "tblMonths" ?

0
 
Jeffrey CoachmanMIS LiasonCommented:
In a nutshell you should always build your Aggregate functions one step at a time;
Does this work:
Dsum("YourField","YourTable")
If no, then don't even bother adding any criteria.

Does this work:
Dsum("YourField","YourTable","SomeField=" & me.SomeValue)
If not then don't even bother adding an "And "

So instead of posting a complex function, that you have no idea of what is wrong,
...Instead test it step by step, so you can tell us where it failed.

Make sense?

;-)

Jeff
0
 
maliqsmomAuthor Commented:
Hi

The entris are tblMonths are MonthID and MonthName

i.e:

1 - Apr
2 - May
3 - Jun
4 - Jul

etc.
0
 
maliqsmomAuthor Commented:
boag

I tried Dsum("YourField","YourTable") and it doesn't work.

I created a new query and typed DSum("Actual Sales","tblRawDataSub")

I am now getting a popup message saying
Syntax error (missing operator) in query expression 'Sum(Actual Sales)'.
0
 
Jeffrey CoachmanMIS LiasonCommented:

Please clearly define:
"doesn't work"

...Then if you used your actual filed names, then this would mean that "YourField" may not be numerical.

So again, how about creating a *simple* table an testing the syntax...?

How about simply posting a sample of this DB?

You have some of the top experts here "Guessing" on all the possible issues...


Do this as an example you can use as a reference:
If you get a copy of the Northwind sample database

...and put this as the controlSource of a textbox on a form:
=DSum("Freight","Orders")
... it should return 64942.69
If you do this:
=DSum("Freight","Orders","EmployeeID=" & 5)
it will return: 3918.71

If you do this:
=DSum("Freight","Orders","EmployeeID=" & 5 & " AND ShipCountry='USA'")
 you will get: 359.34

So you see the syntax works, so if it does not,  then you have to make sure of your spelling and datatypes



JeffCoachman
0
 
mbizupCommented:
If your fieldnames, tablenames, etc contain spaces or various other special characters, or reserved words, you need to enclose them in square brackets, like this:

[Actual Sales]

If your naming convetions avoid these things (such as using ActualSales with no space instead), you don't need the brackets.  Although it may not be reasonably possible to change existing field names, it is a good idea to avoid the use of spaces and special characters in your naming conventions.

The Reddick VBA naming convention is a good one to follow:
www.xoc.net/standards/rvbanc.asp

Standards for Access are midway down the page.
0
 
mbizupCommented:
Just clarifying the above comment - If your names do include spaces and you don't use brackets around them you WILL get errors.

Best to avoid spaces altogether, but if you can't change the field name, use brackets.
0
 
maliqsmomAuthor Commented:
hi jeff

your suggestion involves adding a textbox to a form...i really need the formula to work in a query.

does that matter in this case?
0
 
Jeffrey CoachmanMIS LiasonCommented:
...and just in case it has not been mentioned before, ...Check your spelling...
Then name of the controls on the form may not be that same as the field names in the table:

For example:
=Dsum("YourField","YourTable","EmpID=" & me.txtEmployeeID)

In this case, notice that the Field name is "EmpID", yet the name of the control is "txtEmployeeID"
0
 
Jeffrey CoachmanMIS LiasonCommented:

Can you just post a sample of this DB to avoid confusion...?
Then I am sure any expert involved can solve this simple issue...

At this point it just seems like this is just a syntax/spelling issue, and I don't think any experts here even cares about the points...

If you are saying that if you do this:
Dsum("[Actual Sales]", "tblRawDataSub")
...it does not work, then this is a spelling issue...

...Perhaps the *Real* name of the field (as defined in the table, ...not what you see as the Caption) may be "ActualSales"

This makes no difference if this is a query or a form.

Finally note that even if you get a correct result here, it will still not be a "Running" total as you are requesting.
...and also note that you never actually said what your original error was, nor did you post the SQL you are using.

Again, if you post a sample of this Database, I am sure any expert will be able to get you going.

JeffCoachman
0
 
Gustav BrockCIOCommented:
Several typos, so try with:

RunningSum: DSum("[Actual Sales]","tblRawDataSub","RawDataMainID = '" & [RawDataMainID] & "' And MonthID <= " & [tblMonths]![MonthID] & "")

or just:


RunningSum: DSum("[Actual Sales]","tblRawDataSub","RawDataMainID = '" & [RawDataMainID] & "' And MonthID <= " & [MonthID] & "")

/gustav
0
 
maliqsmomAuthor Commented:
Hi all

Kindly accept my apologies for taking so long to respond.

Please find a sample db (along with the test query) attached for your review.

What I am trying to achieve is a running sum by RawDataMainID.  So the formula should be able to calculate a running sum starting from 1 to 12 and then starting a new running sum for the next RawDataMainID.


Database14.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then you could use a report for this, where there are groping and a running sum options to do this specifcally...

Then there is no need for these Dsum machinations

Sample and screenshot attached

JeffCoachman
Database141.accdb
untitled.JPG
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 You don't want to be using a domain function in a query.  Instead, create a seperate query that does the summing, then use that query as a "table" in a second query and join to it like you normally would in getting data.

Jim.

PS. If no one jumps on your sample DB, I'll do it a little latter.
0
 
maliqsmomAuthor Commented:
Solution was exactly what I needed!

Thank you to all of those persons who assisted with this question.

Stacey
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.