Solved

Progressive totals in a crosstab query

Posted on 2014-07-23
10
299 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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 

Author Comment

by:darls15
Comment Utility
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)
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:darls15
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Works perfectly the way I need, thank you so much :)
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Your welcome.

Ron
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now