Running Totals - cumulative total? (1 Viewer)

[LambellaJules]

Hi, I have a crosstab report that shows bookings for an event grouped on the date booked/week. Data displays fine for weekly totals, e.g. 5, 10, 0, 7. But I also need a cumulative set of data, e.g. 5, 15, 15, 22. Running Total works fine where there is a positive value for each week, but returns 0 for weeks where there are no bookings. I'm pretty sure this must be something that is easily overcome but it's really starting to hurt banging my head. Can anyone out there help? Please keep it as simple as possible as I'm not a programmer, bit of a idiot really!
Many thanks in advance,
Jules

 

[Trevor G]

Jules welcome to the forum, before you use the cross tab query, create another query with an Expression to counter the negatives or empty field that way it will look for positives then use that as the source for the cross tab query.

 

[LambellaJules]

Trevor, thanks very much for your quick reply. I'm not entirely sure I understand what you mean by Expression (formula perhaps?) but I'll continue trying.

 

[Trevor G]

Create a new query and use your table (s) then drag your fields into the Query Grid then save the Query. Then look at the top for an icon that looks like Excel AutoSum this will open the query Builder. Then add an IIF statement to look at the field and test it for a value <=0 and then create your own option when you look at the query in the Datasheet View it will replace the values to the 0 or what you want but still show all the positive numbers you can rename the Expression to what you want then use that in your Cross Tab and it should resolve your issue.

Watch this on YouTube to help you http://www.youtube.com/watch?v=kL5t70j-USw