DSum running total problem

[cms2012]

I tried deleting all the data but when I do it erases all the controls that are on the form including the chart

 

[crxftw]

Okay, well if you are able to get your query working then these will help you with your graph:

MS-Access and Graph Charts
MS-Access and Graph Chart-2

 

[cms2012]

Thanks for the help. I really appreciate you taking the time to work with me.

 

[crxftw]

If these graphs are not what you are looking for you also have an option to take a look at the pivot charts that may be easier to set up.

 

[cms2012]

Ok, so I have got the query to work by using a subquery seen below:

SELECT CHARTDATA2.SALES_PERSON_ID, CHARTDATA2.EMPL_LAST_NAME, CHARTDATA2.JE_PERIOD_ENDING_DATE, CHARTDATA2.metMonth, CHARTDATA2.metYear, CHARTDATA2.TOTAL_SALES, (SELECT Sum(TOTAL_SALES) FROM CHARTDATA2 AS Q WHERE Q.EMPL_LAST_NAME = CHARTDATA2.EMPL_LAST_NAME AND q.metYear=CHARTDATA2.metYear and q.JE_PERIOD_ENDING_DATE <=CHARTDATA2.JE_PERIOD_ENDING_DATE) AS RunTotal
FROM CHARTDATA2
ORDER BY CHARTDATA2.EMPL_LAST_NAME, CHARTDATA2.JE_PERIOD_ENDING_DATE;

Now the problem is that when I try to use it in a chart it is saying that a multi-level group by clause is not allowed in a subquery. I have looked on the forum for a solution to this but I am extremely new to subqueries and don't really understand what they mean. Could you help me please!

 

[crxftw]

Sub-query is just a select statement inside another query (query inside a query). But I really think you are able to manage to put this all into one simple select query instead of trying to make it too complex which makes the whole thing a lot harder.

As I understand then what you need is just a query that pulls data from your table called TOTAL_SALES? which already contains all the fields you need to display it on a graph. A sales person, date (year/month) and sum of total sales. These are basically all the 3 fields you need to make simple line graph. I can't assist any further without seeing the structure of the form or table itself.