DSum running total problem

[cms2012]

Hey everyone,

I have a query that I am using to try to get the running total of a person's sales over a year for multiple years. The query has four fields: SALES_PERSON_ID (AUTONUMBER), EMPL_LAST_NAME, TOTAL_SALES, PERIOD_ENDING_DATE. I have added the last field as the running total with the code:

RunTot: DSum("[TOTAL_SALES]", "[TOTAL SALES]", "Format([PERIOD_ENDING_DATE], 'yyyymm') = '" & Format([PERIOD_ENDING_DATE], 'yyyymm') & "' AND [PERIOD_ENDING_DATE]-1 < #" & [PERIOD_ENDING_DATE] & "#")

It comes up with a total but it is completely wrong. For example, the first person's sales are $587,445.15 but the RunTot is $5,548,437.27. Can someone help please cause I have no idea why it is doing this?:banghead:

 

[pbaldy]

You haven't included the person in the criteria, so you get everybody.

 

[crxftw]

Hi.

Instead of using DSum, have you tried to just Sum the [TOTAL SALES] and on query design add your needed criteria to your date (which you are missing in DSum function so everything gets summed up).

 

[Bryan]

Edit: Posted in parallel to the two above me . . . sheesh!

Is $5,548,437.27 the total of EVERYONE's sales? My guess is that's occurring because there is no condition criteria that tells the expression whose sales to total.

It sounds like you want your query to total sales for a given period for a specified employee and are adding this field to get a global total??? I don't think this approach is going to work, but I don't a a solution either.

 

[cms2012]

Thanks for the replies, pbaldy I changed the code to look like this:

RunTot: Format(DSum("[TOTAL_SALES]","[TOTAL SALES]","EMPL_LAST_NAME='" & EMPL_LAST_NAME & "', "Format([JE_PERIOD_ENDING_DATE], 'yyyymm') = '" & Format([JE_PERIOD_ENDING_DATE],'yyyymm') & "' AND [JE_PERIOD_ENDING_DATE]-1 < #" & [JE_PERIOD_ENDING_DATE] & "#"),"Currency")

but I am still getting the same thing.

Crxftw, would I use the same criteria that I have in the DSum in the criteria section for it to work or would I have to use specific dates?

 

[crxftw]

You would use the criteria only for the date as you need.

Edit: Also if it's just meant for one employee then you of course need a criteria for that as well depending on how you are planning to use it.

 

[cms2012]

The reason that I am trying to use this is that I am trying to get the totals for the year so that I have the right data to put in a chart. The chart needs to be a line chart that is constantly increasing instead of showing the points as they increase and decrease. Sorry if that is confusing. If you know an easy way of getting this to happen on the chart creation when you input a chart in the form, I could do that too.

 

[crxftw]

Charts are usually based on a lot of data. As I see what you want to achieve is a query for one employee and Her yearly totals which for me seems too little data to use in graph context. If you could elaborate on that more maybe could find a solution for that.

 

[cms2012]

Ok I have a form that has three comboboxes. They choose a year, a month and a saleperson. When the selections have been made it autopopulates a bunch of text boxes with certain data pertaining to that person. I need the chart to show the sales of that person for that year.

 

[crxftw]

And the graph itself? Simple line graph, Y-axis shows the amount, X-axis shows dates throughout the year? Graph needs at least 3 data fields.

 

[cms2012]

Yea that is how the graph is arranged but what is an example of what the third field of data? Also, I made progress on the query. I got the running total to equal the sales number but it is not summing the data. If you could take a look to see what I am doing wrong I would be greatly appreciative. Here is the code

RunTot: Format(DSum("[TOTAL_SALES]","[TOTAL SALES]","[EMPL_LAST_NAME]='" & [EMPL_LAST_NAME] & "' AND Format([JE_PERIOD_ENDING_DATE], 'yyyymm') = '" & Format([JE_PERIOD_ENDING_DATE],'yyyymm') & "' AND [JE_PERIOD_ENDING_DATE] - 1 < #" & [JE_PERIOD_ENDING_DATE] & "#"),"Currency")

Thanks

 

[crxftw]

Can you explain a bit what is the goal with that criteria, it confuses me a little bit as I see same field names repeating which makes me think it can be done in much easier way. In what format the [JE_PERIOD_ENDING_DATE] is in the table? For having an employee in a criteria I'd suggest using their ID's instead of names.

 

[cms2012]

JE_PERIOD_ENDING_DATE is formatted as a short date, I am trying to get the running total of a salesperson for each year. Meaning that I want Jan+Feb then (Jan+Feb)+ Mar and so on throughout that year and then it needs to restart on the next year. I saw this code on another forum and applied it to mine. I hope I explained it clearly.

 

[crxftw]

Well to just get totals for selected employee that sums up all data done for current year you would want to use a SQL like that, where EmployeeID = 1 or in your case it would be replaced with referenced field on your form. For date we take the year of the short date (we don't need a month here) and let it equal to the current year.

SELECT Sales.EmployeeID, Sum(Sales.SalesAmount) AS TotalSales
FROM Sales
WHERE (((Year([SaleDate]))=Year(Now())))
GROUP BY Sales.EmployeeID
HAVING (((Sales.EmployeeID)=1));

If you need to use it for graph purpose then this query doesn't help as it's needed to have pivot for the months.

 

[cms2012]

This will work but most of the data that I am using is historical data starting from 01/01/2009

 

[crxftw]

For that you change where clause to your form field, in your case one of the combo controls on your form

WHERE (((Year([SaleDate]))=[Forms]![frmTestForm]![txtYear]))

 

[cms2012]

ok it keeps asking for the parameter value of the form controls and i click cancel and pick the choices from the combo boxes but it shows nothing on the chart

 

[crxftw]

What sort of chart are you trying to use? If possible, are you able to share the example of your database to take a look at it?

 

[cms2012]

I can't share the database because the relative data is sensitive info but I am using a chart that you can insert while in the design view of the form.

 

[crxftw]

What about making a copy of it and removing all the data just to take a look of its structure? I have recently worked a lot with graphs and might be able to assist this way.