Is there a way to carry balances forward in access

[57oln]

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??

Thanks in advance

 

[ArthriticPanda]

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>

Spreadsheet Formulas

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

 

[57oln]

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>

Spreadsheet Formulas

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.

 

[ArthriticPanda]

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

 

[Joe4]

Is this an Access or an Excel question?

 

[57oln]

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?

 

[ArthriticPanda]

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

 

[Kreszch68]

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.

 

[Joe4]

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.

 

[57oln]

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