Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Progressive totals in a crosstab query

Posted on 2014-07-23
10
Medium Priority
?
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 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 48

Expert Comment

by:Dale Fye
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 48

Expert Comment

by:Dale Fye
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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