Find the difference in a running total


I receive data from a weekly report that I import into my database for other purposes.  There is a weekly total, that is a cumulative total and continues to increase but what I really want is the difference added.  So for example, I have 7 weeks in my db.  Week 1 is the last week in Dec.  In one column of the raw data, sale this is sales -- I have individuals sales totals, but I'm interested in the total sales by region, so I use a grouped totals query -- using sum.  I am also using totals in other parts of this data, where it works great.  So, what I need is to be able to use a grouped totals query in 2 of the measures, but find the difference from one of the fields in this total query.  So, below, is mocked data of what I'm looking for, assume there are 200 lines of data and each total is grouping them together.

Calls:                 Sales              Avg Volume
500                    125                     20.7
200                    100                     15.5
200                    75                       10.7
100                    50                       15.5
50                      25                        10.5
100                    20                       15.8

There is no relevance of any of the metrics above.  So, in column 2, the sales are cumulative.  I want to show how many sales per week.  And, in this example, lets say I have weeks ascending, so the last column is actually the end of the year 2007.  If I want to start fresh and see how many sales per week in 2008, how would I do this on column 2?  Ideally, it should give me something like 5 sales in week 1 2008 (because we don't want to count the 20 sales from 2007 -- we want to minus them out) and then in week 2, it would be 25 sales that week, because we have 50 total, but 5 of them are carried over from week 1 2008...and so then in week 3 of 2008, we would have 25 again, so at this point we really have 55, not 75 because we don't want to use the 20 from 2007.  I hope this makes sense.  What I need is a total of sales by week -- not a cumulative total which is what the data is giving me, but by comparing each week, its easy to figure out what the added sales are from the prior week.  I'd like to do this in a query automatically....I think I can use DSum -- but I'm not that familiar with it.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Something like this should do it but you need to find someway to relate the weeks to each other. In this case I've linked them by date - I'm guessing your table has got this.

SELECT, Sales.calls, Sales.sales, Sales.avgvol,
[sales].[sales]-[sales_1].[sales] AS SalesThisWeek
FROM Sales, Sales AS Sales_1
WHERE ((([sales_1].[date]+7)=[sales].[date]));

Hope that helps.
AliciaVeeAuthor Commented:

I'm not sure I follow you.  I have one query that combines two tables to provide the 3 fields.  I don't think I was here it goes again.  The query has all the records, but totaled, and for the 2nd column, I want to find the difference from the prior week.  Let's make it even simpler -- the data displayed after the query runs is as below:

Week      CurWk Leads      CurWk Sales          Avg Wkly Vol
1/8/2008      571                                   552             3.47
1/15/2008      788                                            586           3.67
1/22/2008      602                                   595            3.63

In the 2nd colunn -- these are true numbers for the week.  In the 3rd colum (under sales) this is cumulative.  How can I create a new column that privides the numbers, such as 34 sales for the 2nd week, and 9 sales for the third week?  Are you saying I have to split them up into separate tables??  Below is my sql for this query:

SELECT tWkRptStatus.Week, Sum(tWkRptStatus.CurWkTotal) AS [CurWk Leads], Sum(tWkRptClosed.ClosedSale) AS [CurWk Sales], Avg(tWkRptStatus.AvgWkVol) AS [Avg Wkly Vol]
FROM tWkRptStatus LEFT JOIN tWkRptClosed ON (tWkRptStatus.Office = tWkRptClosed.Office) AND (tWkRptStatus.FSA = tWkRptClosed.FSA) AND (tWkRptStatus.Week = tWkRptClosed.Week)
GROUP BY tWkRptStatus.Week
ORDER BY tWkRptStatus.Week;

Open in new window

Just so that we're clear that's exactly what my query is doing. If you point my query at your query it'll give you your answer.

To make it even simpler:

SELECT YourQueryName.Week, YourQueryName.[CurWk Leads], YourQueryName.[CurWk Leads], YourQueryName.[Avg Wkly Vol],
[YourQueryName].[CurWk Leads]-[sales_1].[CurWk Leads] AS SalesThisWeek
FROM YourQueryName, YourQueryName AS Sales_1
WHERE ((([sales_1].[Week]+7)=[YourQueryName].[Week]));

This should work though I've just hacked it manually so there may be some syntax errors in there - hopefully you get the general idea of what it's going.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

AliciaVeeAuthor Commented:

Okay -- I think I know what you are doing -- so, I take my query -- that I've made, and I add it again to a new query and make an alias?  Hense, Sales_1?  I tried to use your SQL but got errors.  Let me keep trying.
AliciaVeeAuthor Commented:
Wow!  This is really neat!  It worked perfectly.  I have not done a query like this -- where the tables are not joined -- very cool.  Is this what they call a UNION query?  Excellent job!  Thanks so much!!
Nope it's not a UNION query. That's a different thing all together.

This is essentially a standard query but using an alias on the tables/queries to link them back to each other to return different rows on the same row. Does that make sense.

The not using INNER JOIN etc. is how we used to join tables in the olden days - the link is provided by the WHERE clause.

Glad you like it.
AliciaVeeAuthor Commented:

Thanks yes, this worked very well for me.  I'm trying to get a handle on all the various queries available, I have the inner and outer joins down pretty well -- all the others are still new to me.

I'm going to post another question -- somewhat similiar to this one.  I tried your solution on my new issue -- but couldn't get it to work -- its a little more complex than this one -- due to the counts being done in the query.  Maybe you can help?  I'll post the question number once I type it in.  Again, thanks for your help.
AliciaVeeAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.