How do I total multiple fields from a single table?

[AmDigPC]

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):

SELECT 
  [KB_History].[Total Views]-[Previous].[Total Views] AS MonthlyViews
, KB_History.[Total Views]
, KB_History.[Article Number]
, KB_History.Title
, KB_History.[Version Number]
, KB_History.[Created Date]
, KB_History.[Last Modified Date]
, KB_History.[Created By: Full Name]
, KB_History.[Last Modified By: Full Name]
, KB_History.MonthNum
, KB_History.Year
FROM KB_History 
LEFT JOIN KB_History AS Previous 
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):

CREATE TABLE tblMonthlyViews (
    MonthlyViews INTEGER,
    [Total Views] INTEGER,
    [Article Number] CHAR(255),
    [Title] CHAR(255),
    [Version Number] INTEGER,
    [Created Date] DATE,
    [Last Modified Date] DATE,
    [Created By: Full Name] CHAR(255),
    [Last Modified By: Full Name] CHAR(255),
    MonthNum INTEGER,
    Year INTEGER);
 

SELECT *
INTO tblMonthlyViews
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!

[jforbes]

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:

SELECT 
  KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
, Previous.MonthNumber
, KB_History.[Total Views]
, KB_History.[Article Number]
, KB_History.MonthNumber
, KB_History.Title
, KB_History.[Version Number]
, KB_History.[Created Date]
, KB_History.[Last Modified Date]
, KB_History.[Created By Full Name]
, KB_History.[Last Modified By Full Name]
FROM KB_History 
LEFT JOIN KB_History AS Previous 
ON (KB_History.MonthNumber+(KB_History.Year*12)-1=Previous.MonthNumber+(Previous.Year*12)) 
AND (KB_History.[Article Number]=Previous.[Article Number])
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.

[AmDigPC]

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!

[jforbes]

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:

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

[AmDigPC]

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?

[AmDigPC]

Here's the query I have so far:

SELECT 
    KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
    , Previous.MonthNum
    , KB_History.[Total Views]
    , KB_History.[Article Number]
    , KB_History.Title
    , KB_History.[Version Number]
    , KB_History.[Created Date]
    , KB_History.[Last Modified Date]
    , KB_History.[Created By: Full Name]
    , KB_History.[Last Modified By: Full Name]
    , KB_History.MonthNum
    , KB_History.Year
    FROM KB_History 
    LEFT JOIN KB_History AS Previous 
    ON (KB_History.MonthNum+(KB_History.Year*12)-1=Previous.MonthNum+(Previous.Year*12)) 
    AND (KB_History.[Article Number]=Previous.[Article Number])
    WHERE KB_History.MonthNum+(KB_History.Year*12)>=((DatePart("yyyy", Now())*12)+datepart("m", Now())-3);
 
UNION
SELECT KB_History.[Article Number], SUM(MonthlyViews) AS 3MonthViews
, KB_History.[Total Views]
, KB_History.[Article Number]
, KB_History.Title, KB_History.[Version Number]
, KB_History.[Created Date]
, KB_History.[Last Modified Date]
, KB_History.[Created By: Full Name]
, KB_History.[Last Modified By: Full Name]
, KB_History.MonthNum, KB_History.Year
FROM KB_History
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...

[twinnyfo]

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:

Query 1       Query 2
MonthlyViews  Article Number
MonthNum      3MonthViews
TotalViews    TotalViews
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.

[AmDigPC]

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:

SELECT KB_History.[Total Views]-Previous.[Total Views] AS MonthlyViews
    , Previous.MonthNum
    , KB_History.[Total Views]
    , KB_History.[Article Number]
    , KB_History.Title
    , KB_History.[Version Number]
    , KB_History.[Created Date]
    , KB_History.[Last Modified Date]
    , KB_History.[Created By: Full Name]
    , KB_History.[Last Modified By: Full Name]
    , KB_History.MonthNum
    , KB_History.Year
    FROM KB_History 
    LEFT JOIN KB_History AS Previous 
    ON (KB_History.MonthNum+(KB_History.Year*12)-1=Previous.MonthNum+(Previous.Year*12)) 
    AND (KB_History.[Article Number]=Previous.[Article Number])
    WHERE KB_History.MonthNum+(KB_History.Year*12)>=((DatePart("yyyy", Now())*12)+datepart("m", Now())-3);

qry3MonthTotal:

SELECT SUM(MonthlyViews) AS 3MonthViews
    , tblMonthlyViews.[Total Views]
    , tblMonthlyViews.[Article Number]
    , tblMonthlyViews.Title
    , tblMonthlyViews.[Version Number]
    , tblMonthlyViews.[Created Date]
    , tblMonthlyViews.[Last Modified Date]
    , tblMonthlyViews.[Created By: Full Name]
    , tblMonthlyViews.[Last Modified By: Full Name]
    , tblMonthlyViews.MonthNum
    , tblMonthlyViews.Year
FROM tblMonthlyViews
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?

[twinnyfo]

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.

[AmDigPC]

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!

[Rabbit]

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.

[jforbes]

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:

SELECT 
  Sum(qry3MonthViews.MonthlyViews) AS SumOfMonthlyViews
, qry3MonthViews.[Article Number]
, qry3MonthViews.[Version Number]
FROM qry3MonthViews
GROUP BY 
  qry3MonthViews.[Article Number]
, qry3MonthViews.[Version Number]

[AmDigPC]

@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.