469,126 Members | 1,277 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

How do I total multiple fields from a single table?

12
This is a cary-over from this thread:
Run Query based off Table selected from Combo Box

Info about database/tables: I have a database (Access 2013) with a table in it that stores a history of Knowledgebase article view statistics. There is a snapshot of Total (lifetime) Views taken each month and that data is added to the table with the appropriate month & year tag (2 separate fields: MonthNum & Year).

I have the following query (thx to help from jforbes) titled qryMonthlyViews that calculates the Monthly Views (calculating the difference in views from month-to-month):
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   [KB_History].[Total Views]-[Previous].[Total Views] AS MonthlyViews
  3. , KB_History.[Total Views]
  4. , KB_History.[Article Number]
  5. , KB_History.Title
  6. , KB_History.[Version Number]
  7. , KB_History.[Created Date]
  8. , KB_History.[Last Modified Date]
  9. , KB_History.[Created By: Full Name]
  10. , KB_History.[Last Modified By: Full Name]
  11. , KB_History.MonthNum
  12. , KB_History.Year
  13. FROM KB_History 
  14. LEFT JOIN KB_History AS Previous 
  15. ON (KB_History.MonthNumber-1 = Previous.MonthNumber) AND (KB_History.[Article Number] = Previous.[Article Number]);
I then wrote the following two statements to create a new table (qryCreateTable) and import the data from the query to the new table (qryImportViewData):

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE tblMonthlyViews (
  2.     MonthlyViews INTEGER,
  3.     [Total Views] INTEGER,
  4.     [Article Number] CHAR(255),
  5.     [Title] CHAR(255),
  6.     [Version Number] INTEGER,
  7.     [Created Date] DATE,
  8.     [Last Modified Date] DATE,
  9.     [Created By: Full Name] CHAR(255),
  10.     [Last Modified By: Full Name] CHAR(255),
  11.     MonthNum INTEGER,
  12.     Year INTEGER);
  13.  
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. INTO tblMonthlyViews
  3. FROM qryMonthlyViews;
Where I'm stuck is, I don't know how I would write the query that would total the last 3 months. Can anyone help on this?

Thank you in advance!
Oct 16 '14 #1
12 1354
jforbes
1,107 Expert 1GB
Hello again AmDigPC,

I copied your Query into my test DB and got things mostly in sync. I like how you added the Year in as a separate column and took that into account with the Query so that when the year rolls over your Query will continue to work. The magic this time is being done in the Where clause. It will give you totals for the last three three months. This will roll and follow the Date so when we enter a new Month the records returned will reflect it.

Again, you will have to tweak this to make it work for you:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
  3. , Previous.MonthNumber
  4. , KB_History.[Total Views]
  5. , KB_History.[Article Number]
  6. , KB_History.MonthNumber
  7. , KB_History.Title
  8. , KB_History.[Version Number]
  9. , KB_History.[Created Date]
  10. , KB_History.[Last Modified Date]
  11. , KB_History.[Created By Full Name]
  12. , KB_History.[Last Modified By Full Name]
  13. FROM KB_History 
  14. LEFT JOIN KB_History AS Previous 
  15. ON (KB_History.MonthNumber+(KB_History.Year*12)-1=Previous.MonthNumber+(Previous.Year*12)) 
  16. AND (KB_History.[Article Number]=Previous.[Article Number])
  17. WHERE KB_History.MonthNumber+(KB_History.Year*12)>=((DatePart("yyyy", Now())*12)+datepart("m", Now())-3);
If you would ever want to have the User select the cutoff date, get in and monkey around with the last part of the Where clause. Also if you are looking for a Single Number per Article to represent the Total for the Three months, you can create an additional Query, an Aggregate Query, with this as its source and Sum up based on Title.
Oct 16 '14 #2
AmDigPC
12
Thank you again jforbes!

So this new query you provided is doing the same thing as the last one (calculating the monthly views), but now it's taking into account the year right?

I do need to total the number of views for the 3 previous months (single number for each article). I was thinking I would need to do some kind of array to accomplish this but you're saying an 'Aggregate Query' is the way to go? I'm not familiar with these so I'll have to do some research and see if I can figure it out...

I'll post back later if I run into trouble!
Oct 17 '14 #3
jforbes
1,107 Expert 1GB
Yes, The Query in Post #2 takes into account the Year. It should be a rough equivalent to your qryMonthlyViews, with the Addition of the Year and the Addition of the Where clause to limit the records to the last three months.

Aggregate Queries are really just Queries that are used to group records to perform some type of Summation, Count, Average or other type of Aggregate function upon. Like the following would return a count of how many Male Employees a company has:
Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT(EmployeeID) FROM Employees WHERE Gender='M'
In Access you can click on the Totals Button on the Ribbon or View|Totals in earlier versions to start manipulating these through the Design View.

There is probably enough here for me to put this together for you, but I think you might benefit from the Homework. =)

Good luck
Oct 17 '14 #4
AmDigPC
12
OK, so I've been messing with this for a week now and cannot figure out how to get this 'Aggregate Query' to work... I've managed to get it to total ALL the views in the data set (not even sure how I did this) but I cannot get it to give me a total of the last 3 months... Any ideas on how to do this?
Oct 27 '14 #5
AmDigPC
12
Here's the query I have so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
  3.     , Previous.MonthNum
  4.     , KB_History.[Total Views]
  5.     , KB_History.[Article Number]
  6.     , KB_History.Title
  7.     , KB_History.[Version Number]
  8.     , KB_History.[Created Date]
  9.     , KB_History.[Last Modified Date]
  10.     , KB_History.[Created By: Full Name]
  11.     , KB_History.[Last Modified By: Full Name]
  12.     , KB_History.MonthNum
  13.     , KB_History.Year
  14.     FROM KB_History 
  15.     LEFT JOIN KB_History AS Previous 
  16.     ON (KB_History.MonthNum+(KB_History.Year*12)-1=Previous.MonthNum+(Previous.Year*12)) 
  17.     AND (KB_History.[Article Number]=Previous.[Article Number])
  18.     WHERE KB_History.MonthNum+(KB_History.Year*12)>=((DatePart("yyyy", Now())*12)+datepart("m", Now())-3);
  19.  
  20. UNION
  21. SELECT KB_History.[Article Number], SUM(MonthlyViews) AS 3MonthViews
  22. , KB_History.[Total Views]
  23. , KB_History.[Article Number]
  24. , KB_History.Title, KB_History.[Version Number]
  25. , KB_History.[Created Date]
  26. , KB_History.[Last Modified Date]
  27. , KB_History.[Created By: Full Name]
  28. , KB_History.[Last Modified By: Full Name]
  29. , KB_History.MonthNum, KB_History.Year
  30. FROM KB_History
  31. GROUP BY [Article Number];
When I run this, Access tells me "Your query does not include the specified expression 'Total Views' as part of an aggregate function"

I'm not sure what this means or how to fix it...
Oct 28 '14 #6
twinnyfo
3,653 Expert Mod 2GB
AmDigPC,

You may need to use an aggregate expression for your "Total Views" field in the second query (probably a SUM() function).

Also, when you use a UNION Query, all fields in both queries must be identical. They do not need to have identical names, but they must refer to the same field in the output.

Based on your query, this is what the DB is seeing:

Expand|Select|Wrap|Line Numbers
  1. Query 1       Query 2
  2. MonthlyViews  Article Number
  3. MonthNum      3MonthViews
  4. TotalViews    TotalViews
  5. Etc....
This may be intentional, but it is difficult to believe that "MonthlyViews" is the same type of data returned with "Article Number".

Not sure how much this helps.... Without having my fingers on your Queries/Tables, it is sometimes difficult to troubleshoot via forum.
Oct 28 '14 #7
AmDigPC
12
So I decided to scrap writing the SUM query as part of the initial query to get the monthly views (that's apparently over my head because I can't get tit to work)... What I've done is dumped the data from qry3MonthViews into a table then just created a separate SUM query (qry3MonthTotal)to add up the monthly views for each article (simple enough right?!). The problem is, I keep getting the "Your query does not include the specified expression 'Total Views' as part of an aggregate function" error every time I try to run my SUM query

qry3MonthViews:
Expand|Select|Wrap|Line Numbers
  1. SELECT KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
  2.     , Previous.MonthNum
  3.     , KB_History.[Total Views]
  4.     , KB_History.[Article Number]
  5.     , KB_History.Title
  6.     , KB_History.[Version Number]
  7.     , KB_History.[Created Date]
  8.     , KB_History.[Last Modified Date]
  9.     , KB_History.[Created By: Full Name]
  10.     , KB_History.[Last Modified By: Full Name]
  11.     , KB_History.MonthNum
  12.     , KB_History.Year
  13.     FROM KB_History 
  14.     LEFT JOIN KB_History AS Previous 
  15.     ON (KB_History.MonthNum+(KB_History.Year*12)-1=Previous.MonthNum+(Previous.Year*12)) 
  16.     AND (KB_History.[Article Number]=Previous.[Article Number])
  17.     WHERE KB_History.MonthNum+(KB_History.Year*12)>=((DatePart("yyyy", Now())*12)+datepart("m", Now())-3);
qry3MonthTotal:
Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(MonthlyViews) AS 3MonthViews
  2.     , tblMonthlyViews.[Total Views]
  3.     , tblMonthlyViews.[Article Number]
  4.     , tblMonthlyViews.Title
  5.     , tblMonthlyViews.[Version Number]
  6.     , tblMonthlyViews.[Created Date]
  7.     , tblMonthlyViews.[Last Modified Date]
  8.     , tblMonthlyViews.[Created By: Full Name]
  9.     , tblMonthlyViews.[Last Modified By: Full Name]
  10.     , tblMonthlyViews.MonthNum
  11.     , tblMonthlyViews.Year
  12. FROM tblMonthlyViews
  13. GROUP BY [Article Number];
There's gotta be something I'm doing wrong here, I just can't figure out what it is... Can anyone show me how this query should be written?
Oct 29 '14 #8
twinnyfo
3,653 Expert Mod 2GB
AmDigPC,

Would it be possible to dump your Tables into an Excel Spreadsheet and upload that file? I can't access DBs or Zip files at work. I have a concept of what you are trying to do, but without data, it's not cementing in my mind.

Of course, if there is any confidential or proprietary information, please exclude that. I'd just like to take a look at what you have and work from that.
Oct 29 '14 #9
AmDigPC
12
twinnyfo,
Attached is an export of the data set I'm working with (minus the 'Created By: Full Name' & 'Last Modified By: Full Name' columns).

Thanks for looking into this!
Attached Files
File Type: xlsx KB_History.xlsx (113.2 KB, 293 views)
Oct 29 '14 #10
Rabbit
12,516 Expert Mod 8TB
When you do an aggregate query, every field in the SELECT clause needs to be either in an aggregate function or in the GROUP BY clause.
Oct 29 '14 #11
jforbes
1,107 Expert 1GB
I'm sorry to hear that you are struggling with this. I’ve been a bit busy of late, so I just now got back to check on this tread.

Aggregate Queries are powerful but finicky. Every column in the query needs to be an Expression or part of the Group By clause. So columns like CreateDate, MonthNum and LastModified aren’t typically included as they have no impact on the Expression that is being fulfilled. It takes a bit to get used to it as typically the first instinct is to include all the fields and do some summations like you would in Excel. So I would try this for you Aggregate Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   Sum(qry3MonthViews.MonthlyViews) AS SumOfMonthlyViews
  3. , qry3MonthViews.[Article Number]
  4. , qry3MonthViews.[Version Number]
  5. FROM qry3MonthViews
  6. GROUP BY 
  7.   qry3MonthViews.[Article Number]
  8. , qry3MonthViews.[Version Number]
Oct 30 '14 #12
AmDigPC
12
@jforbes: Thanks again for helping me out! I figured I was making some rookie mistake in the formatting/structure... I had managed to get it to do the actual sum but it would ONLY show the column that was summed.
Oct 31 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ben Willcox | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.