Solved

Progressive totals in a crosstab query

Posted on 2014-07-23
10
302 Views
Last Modified: 2014-07-27
Hi Experts

In my database I have a table for recording views which is added to by users each month.

I have based a select query which groups recordings by date and returns the max views for the month. As each month's recording views are added this will automatically adjust, e.g. last month in the crosstab is "2014 07" and next month when I run the query "2014 08" will be added.

I have then created a crosstab query to display each recording and its views month by month. Some months have no views recorded.

What I need to happen is for this crosstab to be progressive, e.g. if in the first month recorded, 2012 03, there was 8 views but no views in 2014 04. I need 2012 04 to be 8, otherwise just report the views for 2014 04. This is the same for each month progressively. Example below:

Currently...
Recording Name                                        2012 03       2012 04      2012 05          2012 06
Recording 1318804169845                           8                    0                      10                0

Needed...
Recording Name                                        2012 03       2012 04      2012 05          2012 06
Recording 1318804169845                           8                    8                      10                10

I have attached a sample database to help explain and would appreciate any help on how to accomplish this.

Thanks
darls15
Example-24072014.accdb
0
Comment
Question by:darls15
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 40216272
I tried doing this at first with a nested query but it made the crosstab take forever to open.  So instead I used some vba code to populate a temporary table and this version is quite fast.  I also added a macro to call the code so just double click on the macro called "ShowNumberOfRecordingsViews" and see if the resulting crosstab is what you wanted.

Ron
Example-24072014-rev.accdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40216438
Seems a little confusing.  Why would you want to mask the fact that you had no views of a particular recording during a given month?  Just looking at the output, you have no way of knowing that the values for April and June are not true.
0
 

Author Comment

by:darls15
ID: 40218093
Hi Ron

Thanks for your reply, There seems to be something wrong with the attached file. When I click the link and it opens in a browser window with lots of symbols etc.

Thanks
darls15
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:darls15
ID: 40218100
Hi Dale

My apologies for the confusion. The aim of what I need isn't to mask that fact there are no views for the month, but to show the progression of views up to the current date. I am able to ascertain at at time from my current query the views for each month, this is just a different report that is required.

Cheers
darls15
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40218109
Try right clicking on the file and saving it.

so, what you really want is

Recording Name                                        2012 03       2012 04      2012 05          2012 06
Recording 1318804169845                           8                    8                   18                 18

which reflects the cumulative views by recording through each of the months?
0
 

Author Comment

by:darls15
ID: 40218137
Hi Dale

No, not quite, progressive is probably the wrong word I'm using I guess. I'll try to explain more clearly.

To begin, there was 8 views in "2012 03" and no views to the "2012 04" position. There were 2 views during the "2012 04" to "2012 05" period which brings the total to 10.

So in the "2012 04" position I need to have the previous months total as there were no views during this period. This will be the same for "2012 06" position, where the "2012 05" total is to be placed and so on. I hope this explains better.

Currently...
Recording Name                                        2012 03       2012 04      2012 05          2012 06
Recording 1318804169845                           8                    0                      10                0

Needed...
Recording Name                                        2012 03       2012 04      2012 05          2012 06
Recording 1318804169845                           8                    8                      10                10

Thanks again
darls15
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40218415
There seems to be something wrong with the attached file.
The problem is a bug the site is currently experiencing when using Internet Explorer as your browser.  You can try using another browser such a Chrome or Firefox or you could try Right-clicking the link and using "Save target as..."

If you go with the latter, the file will be saved with a ".htm" extension.  You will need to rename this to a ".accdb" extension.

Ron
0
 

Author Comment

by:darls15
ID: 40218576
Hi Ron

Thanks for this. I'm off for the weekend so I'll test it over the weekend and get back to you on Monday.

Thanks
darls15
0
 

Author Closing Comment

by:darls15
ID: 40223183
Works perfectly the way I need, thank you so much :)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40223508
Your welcome.

Ron
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

821 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