Accumulated values in query

[Firasath]

Hi, I have a question regarding Access Query. My Query results looks like the below one.

ClosedSlab	Consumer	Consumer-Gold	Enterprise	Prestige	SMB
1	30158	20014	4058	7817	4523
2	5240	3582	588	736	640
3	2141	1507	182	195	220
4	1054	664	98	187	118
5	498	253	55	108	55
6	261	147	28	61	31
7	141	85	27	35	22
8	82	40	14	17	10
9	37	15	5	8	4
10	17	11	3	3	5
11	12	2	1	2	
12	6	2		2	2
13	2	1			
	39649	26323	5059	9171	5630

My requirement is to accumulate the values in each column.

Secondly, I would like show the result in accumulated percentages too.

How can I achieve this in Access. Sorry, I could not above align the query results to the column.

 

[ranman256]

I have a 'report' table.
step 1, I sum values and write to the tMoRpt table in the [sum] column (like you see above)
step 2, I calc the percentages and write to the table but in the [pct] column.
step 3, once I write all values, (many stages/depts) it sum this report table to the FINAL table tMoRptFinal
so now every dept has a single record
dept , sum, pct
ACCTG 2151 .75
IT 848 , .47

Then send reports on this final table.

 

[Firasath]

Sorry, didn't get. Are you running separate query to write or using the same query. Please elaborate a little.

The above result pasted is based on the cross tab query where I am checking how many complaints are closed within days specified in the first column. I want to accumulate that. It is very simple in Excel but not able to understand how to implement the same in Access.

 

[Joe4]

Ways to do totals in queries:

1. Do a Totals (Aggregate) Query. The resulting query will just be a single row.
See: Access Tips: Calculating Totals in Queries

2. Add a Totals Row to an Existing Query (may not be available in really old versions of Access).
See: Access 2010: Add a “Total” Row To Apply Mathematical Functions Quickly

 

[Firasath]

Ways to do totals in queries:

1. Do a Totals (Aggregate) Query. The resulting query will just be a single row.
See: Access Tips: Calculating Totals in Queries

2. Add a Totals Row to an Existing Query (may not be available in really old versions of Access).
See: Access 2010: Add a “Total” Row To Apply Mathematical Functions Quickly

Thanks for sharing the links. That is what I did and the above result is the output of it. My requirement is something like illustrated below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]No. of (Day Wise)[/TD]
[TD="align: center"]No of Complaints (Accumulative)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]45[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]65[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]85[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]115[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]135[/TD]
[/TR]
[TR]
[TD="align: center"]Total[/TD]
[TD="align: center"]135[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is it possible with the Access Query? This is option is there in Excel, but could not figure out how to achieve it in Access.

 

[Joe4]

So, you actually are looking for a running sum? Not quite as easy in Access as in Excel, as the order/location of records in Access really has no meaning (like it does in Excel).

Here is a link that shows you how you can do that: https://support.microsoft.com/en-us/kb/290136

 

[Firasath]

So, you actually are looking for a running sum? Not quite as easy in Access as in Excel, as the order/location of records in Access really has no meaning (like it does in Excel).

Here is a link that shows you how you can do that: https://support.microsoft.com/en-us/kb/290136

Thanks. I was just looking for confirmation. I'll try to manage it in some other way then.

 

[Joe4]

Thanks. I was just looking for confirmation. I'll try to manage it in some other way then.

Note it CAN be done, and that link I provided shows you how. It just isn't as simple or straightforward as one might think.

Where people often get in to trouble is thinking that Access is an extension of Excel -- it is not, it is an entirely different program and needs to be thought of differently. Excel is a spreadsheet program, where Access is a Relational Database. The nature of those beasts is very different and serve different purposes.