Progressive totals in a crosstab query

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
darls15Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
IrogSintaConnect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
darls15Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
darls15Author Commented:
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
 
Dale FyeCommented:
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
 
darls15Author Commented:
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
 
IrogSintaCommented:
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
 
darls15Author Commented:
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
 
darls15Author Commented:
Works perfectly the way I need, thank you so much :)
0
 
IrogSintaCommented:
Your welcome.

Ron
0
All Courses

From novice to tech pro — start learning today.