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!
12 1569
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.
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!
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
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?
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...
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.
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?
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.
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!
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.
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]
@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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ben Willcox |
last post by:
Hi I am having difficulty writing an SQL query to do what I want:
I have 1 table with 2 columns, 'id' and 'name':
tbl_names:
id name
-- ----
1 Bob
2 Jeff
3 Fred
|
by: rayone |
last post by:
Hi folks.
I need advice.
2 options, which do you think is the better option to
display/retrieve/report on the data.
Keep in mind reporting (Crystal), SQL Performance, VB Code, usability,...
|
by: PercyPercy |
last post by:
For some reason I can't write the proper query to get a simple total from a single table. Here is the basic table design:
Transaction ID___Product ID___ Transaction Date____Transaction Quantity...
|
by: mkjets |
last post by:
I have worked for hours on trying to find a solution and have not
figured it out. I am working in Access 2003. I need to create a query
that takes values from 1 table and displays them in...
|
by: Howie |
last post by:
Hi all. Hope you can help.
I occasionally need to enter info directly into a table. However,
when I do, I often need to enter the same value into a large
number of fields.
It would be nice if...
|
by: nsymiakakis |
last post by:
Hello everyone, I know Access fairly well, but I am very, very weak on SQL. I have a very large table that I need to pull a grand Total from multiple fields. I have created a query to SUM all the...
|
by: 4Him |
last post by:
First off, let me say this is a great site! I've just started working with Access and much of my success is from what I've read here!
Background: I have a form, driven off a single table.
Goal:...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
...
|
by: MandarkDC |
last post by:
I am a total novice at access, who wants to move his inventory from Excel to Access, and add some functionality. One question that I haven't really seen answered in the general introductions to...
|
by: Bigdaddrock |
last post by:
I have a form that allows for input of new Customer Billing address fields (B1, B2, B3) as well as fields for the Shipping Address (S1, S2, S3).
Sometimes these are the exact same!
I have text...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |