Solved

DSum Function in MS Access query

Posted on 2011-09-02
20
1,064 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:maliqsmom
  • 7
  • 5
  • 3
  • +3
20 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36473891
Try it like this perhaps:

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

0
 
LVL 57
ID: 36473892
<<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36473908
But...[RawDataMainID] looks numeric in your sceenshot, so try:

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

Expert Comment

by:Rey Obrero
ID: 36473928
try this


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


what are the entries in table "tblMonths" ?

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36473933
try this, wrong bracketing


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


what are the entries in table "tblMonths" ?

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36473938
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
 

Author Comment

by:maliqsmom
ID: 36474012
Hi

The entris are tblMonths are MonthID and MonthName

i.e:

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

etc.
0
 

Author Comment

by:maliqsmom
ID: 36474037
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36474274

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
 
LVL 61

Expert Comment

by:mbizup
ID: 36474892
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 61

Expert Comment

by:mbizup
ID: 36474947
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
 

Author Comment

by:maliqsmom
ID: 36475039
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36475054
...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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36475150

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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36486665
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
 

Author Comment

by:maliqsmom
ID: 36587716
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36587884
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
 
LVL 57
ID: 36587900

 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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 36588085

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
 

Author Closing Comment

by:maliqsmom
ID: 36588301
Solution was exactly what I needed!

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

Stacey
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now