asked on

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

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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.

darls15

ASKER

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

darls15

ASKER

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

Dale Fye

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?

darls15

ASKER

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

IrogSinta

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

darls15

ASKER

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

darls15

ASKER

Works perfectly the way I need, thank you so much :)

IrogSinta

Your welcome.

Ron