# Is there a way to carry balances forward in access

#### 57oln

##### New Member
Hi all,

I again needs some help and am somewhat stuck.

I have a report which is grouped by month and then sorted by year. So we can compare work flow for the same month over years. Each month then has a total of unprocessed information. I have tried to demonstrate it in the table below.

 Month Year Queue Job1 Job2 Job 3 1 2012 Post 904 63 24 Processed 376 47 0 Unprocessed total 528 16 24 2013 Post 544 70 26 Processed 308 24 0 Unprocessed total 236 46 26 2 2012 Post 760 139 15 Processed 252 137 0 Unprocessed total 508 2 15

<tbody>
</tbody>

The unprocessed total is a calculated field within the "Year" Footer of the report and the calculation is within the report itself.

I have been asked to have the unprocessed total carry forward. i.e. Job 3 in Month 1 2012 had 24 unprocessed records, so Job 3 in Month 2 2012 should be the 24 from Month 1 plus the 15 in Month 2 giving a total of 39.

This needs to work for Months 1 to 12 and then roll over to Month 1 in the next year.

I have tried to think about it and am leaning towards adding something to something like count the month before to the calculated field, but I have a headache setting in and this is beginning to be over my head......can anyone help??

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi,

I would use an array formula or SUMPRODUCT;

Sheet1

 A B C D E F G H I J 1 Month Year Queue Job 1 Job 2 Job 3 Job 1 Job 2 Job 3 2 1 Post 904 63 24 1056 18 39 3 Processed 376 47 0 4 Unprocessed Total 2012 528 16 24 5 6 Post 544 70 26 7 Processed 308 24 0 8 Unprocessed Total 2013 236 46 26 9 10 2 Post 904 139 15 11 Processed 376 137 0 12 Unprocessed Total 2012 528 2 15 13

<tbody>
</tbody>

 Cell Formula H2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(D1:D12>0),D1:D12) I2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(E1:E12>0),E1:E12) J2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(F1:F12>0),F1:F12) D4 =D2-D3 E4 =E2-E3 F4 =F2-F3 D8 =D6-D7 E8 =E6-E7 F8 =F6-F7 D12 =D10-D11 E12 =E10-E11 F12 =F10-F11

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope this helps.

AP

Hi,

I would use an array formula or SUMPRODUCT;

Sheet1

 A B C D E F G H I J 1 Month Year Queue Job 1 Job 2 Job 3 Job 1 Job 2 Job 3 2 1 Post 904 63 24 1056 18 39 3 Processed 376 47 0 4 Unprocessed Total 2012 528 16 24 5 6 Post 544 70 26 7 Processed 308 24 0 8 Unprocessed Total 2013 236 46 26 9 10 2 Post 904 139 15 11 Processed 376 137 0 12 Unprocessed Total 2012 528 2 15 13

<tbody>
</tbody>

 Cell Formula H2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(D1:D12>0),D1:D12) I2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(E1:E12>0),E1:E12) J2 =SUMPRODUCT(--(\$A\$1:\$A\$12="Unprocessed Total"),--(\$B\$1:\$B\$12=2012),--(F1:F12>0),F1:F12) D4 =D2-D3 E4 =E2-E3 F4 =F2-F3 D8 =D6-D7 E8 =E6-E7 F8 =F6-F7 D12 =D10-D11 E12 =E10-E11 F12 =F10-F11

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hope this helps.

AP

Hi ArthriticPanda,

Is there a way to have the totals in row 12 instead of being out in columns H, I, J?

Thanks.

Hi ArthriticPanda,

Is there a way to have the totals in row 12 instead of being out in columns H, I, J?

Thanks.

Hi 57oln,

Yes you can, in the cell formulas listed above, you can see they are in cells H2, I2 & J2.

Just type the formula into the cells at the bottom of your rows, e.g

A12 =SUMPRODUCT, etc
B12 =SUMPRODUCT, etc

AP

Is this an Access or an Excel question?

Is this an Access or an Excel question?

access, hence the title and why it is posted in the access section.

Also why I called it a report and not a spreadsheet.

Why is that Joe4?

My apologies 57oln, I thought I was following an excel thread. I've done that a couple of times today. (The clue is in the title!)

AP

Sometimes things aren't that complicated as they seem to be. What you need is a running total, which is a standard feature in de Access reporting tool. The only thing you need to do is set the running total option to 'Yes' in the sum field.

access, hence the title and why it is posted in the access section.

Also why I called it a report and not a spreadsheet.

Why is that Joe4?
That is what I thought, but then I saw the "Excel-type" reply, and you replied to that with an Excel-type reply (reply #3).
Just wanted to make sure it was in the correct forum, and if not, I could have moved it for you.

My apologies 57oln, I thought I was following an excel thread. I've done that a couple of times today. (The clue is in the title!)

AP

No problems, I can see how it would easily be done.

Sometimes things aren't that complicated as they seem to be. What you need is a running total, which is a standard feature in de Access reporting tool. The only thing you need to do is set the running total option to 'Yes' in the sum field.

I have tried this and because of the months being grouped together it doesn't work (perhaps I am doing something wrong).

When I try it I get the report doing this

 Month 1 Queue 1 Queue 2 Queue 3 2012 Mail 50 50 50 Processed 20 10 50 Balance unprocessed 30 40 0 2013 Mail 20 20 20 Processed 20 10 0 Balance unprocessed 30 50 20 Month 2 2012 Mail 20 30 50 Processed 0 30 5 Balance unprocessed (when running total by group) 20 0 45 Balance unprocessed (when running total over all) 50 50 65

<tbody>
</tbody>

But what I am looking for is month 1 adding to month 2 and so on and then coming back to month 1 in the new year.
 Month 1 Queue 1 Queue 2 Queue 3 2012 Mail 50 50 50 Processed 20 10 50 Balance unprocessed 30 40 0 2013 Mail 20 20 20 Processed 20 10 0 Balance unprocessed 0 10 20 Month 2 2012 Mail 20 30 50 Processed 0 30 5 Balance unprocessed 50 40 95

<tbody>
</tbody>

That is what I thought, but then I saw the "Excel-type" reply, and you replied to that with an Excel-type reply (reply #3).
Just wanted to make sure it was in the correct forum, and if not, I could have moved it for you.

No worries, thanks

Replies
5
Views
1K
Replies
0
Views
136
Replies
16
Views
543
Replies
6
Views
424
Replies
19
Views
380

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back