Find the difference in a running total

Posted on 2008-02-08
Medium Priority
Last Modified: 2012-06-21

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.
Question by:AliciaVee
  • 5
  • 3

Expert Comment

ID: 20855714
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.date, 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.

Author Comment

ID: 20855948

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 clear...so 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


Accepted Solution

Spot_The_Cat earned 2000 total points
ID: 20855989
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

ID: 20856061

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.

Author Closing Comment

ID: 31429390
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!!

Expert Comment

ID: 20856933
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.

Author Comment

ID: 20858084

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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

624 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