By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Summing A row of data in access

P: 68
hey there
I am working in a crosstab report in which i want to sum the row of data. lets say for eg i have this data as my row:
Expand|Select|Wrap|Line Numbers
  1. Col 1       col2    col3   total
  2.   23           10      1        34
  3.    2            12      9        23
this depicts how my table is. the trouble is I am not getting to compute and arrive at the value in the total column. I tried the following code but got nothing
Expand|Select|Wrap|Line Numbers
  1. Dim gbl As Integer
  2. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  3. gbl = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0)
  4. Col7 = glb
  5. End Sub
Is there a way to get this done? there must be. Help!
Jan 6 '09 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I'm confused. Are you saying the total column is not produced in your query?

If so, then do you have a problem adding values together in a record?
Jan 6 '09 #2

P: 68
yes thats the problem. I want to add the values in the row and gain the total for each row but thhis is evading my simple mind.
Help Please?
Jan 6 '09 #3

NeoPa
Expert Mod 15k+
P: 31,186
OK.

In the absence of your detailed SQL I will try to put something down of a general nature that you can build from :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Col1],
  2.        [Col2],
  3.        [Col3],
  4.        [Col1]+[Col2]+[Col3] AS [Total]
  5.  
  6. FROM MyTable
Line #4 illustrates the basic concept of what you need to do.
Jan 6 '09 #4

P: 68
Here is the SQl I am using. It is a crosstab Query which counts the task that support staff had performed and present them by category. In the report i want to total them per person at the end of each row.

I dont know if I can also include this total in the query. if i can it would be great. in the final analysis i want it to show on the report. How it is accomplish has no restrictions.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [forms]![frmSearch]![Begdate] DateTime, [forms]![frmSearch]![Enddate] DateTime;
  2. TRANSFORM Count(SupportLogs.Status) AS CountOfStatus
  3. SELECT SupportLogs.SupportStaff
  4. FROM SupportLogs
  5. WHERE (((SupportLogs.DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
  6. GROUP BY SupportLogs.SupportStaff
  7. PIVOT SupportLogs.Status;
  8.  
Jan 6 '09 #5

NeoPa
Expert Mod 15k+
P: 31,186
I was rather hoping you'd take the concept and run with it as I don't ever use crosstab queries (and you presumably know your own work).

However, I can see that crosstab queries make implementing this concept particularly difficult. In fact, the only way I can think of atm is by using a subquery.

Let me give it some more thought, and if you don't post saying you have a solution I'll put together a subquery solution. I won't be able to test it easily though as that would involve putting a lot of stuff together.
Jan 6 '09 #6

P: 68
Isn't there an easy way to do it from within the report. I got to sum the entire column and place the total in the report footer using the sum function, but the adding by row is where the difficult comes in.

Should'nt the VB codes work?

Well theyr'e not. Everytime I run the report I get a blank column.
Jan 6 '09 #7

P: 3
as far as i know, vb code can't change text box values, unless you make it as a function, and call the function in the datavalue of the textbox.

ie:
Expand|Select|Wrap|Line Numbers
  1. public function ReturnVal (col2, col3, col4, col5, col6) as long
  2. Dim gbl As Integer 
  3. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) 
  4. gbl = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0) 
  5. ReturnVal = glb 
  6. End function
then in your textbox data value:
Expand|Select|Wrap|Line Numbers
  1. =ReturnVal(txtCol2, txtCol3, txtCol4, txtCol5, txtCol6)
that should get you your desired result.
Jan 6 '09 #8

P: 68
This is the stuff i have been looking for. Just a little correction and the program worked fine.

Here is the working code
Expand|Select|Wrap|Line Numbers
  1. Public Function ReturnVal(txCol2, txCol3, txCol4, txCol5, txCol6) As Long
  2.     Dim gbl As Long
  3.     glb = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0)
  4.     ReturnVal = glb
  5. End Function
Some of the changes were just to accomodate my preference but others were necessary

Thanks guys for making my life sooooo much easier
Neopa if you got the query then posting it wouldn't hurt. I'll be glad to learn something new.thanks again
Jan 6 '09 #9

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [forms]![frmSearch]![Begdate] DateTime,
  2.            [forms]![frmSearch]![Enddate] DateTime;
  3.  
  4. SELECT *,
  5.        (Nz([Col2],0)
  6.        +Nz([Col3],0)
  7.        +Nz([Col4],0)
  8.        +Nz([Col5],0)
  9.        +Nz([Col6],0)) AS Total
  10.  
  11. FROM (TRANSFORM Count(SupportLogs.Status) AS CountOfStatus
  12.       SELECT SupportLogs.SupportStaff
  13.       FROM SupportLogs
  14.       WHERE (((SupportLogs.DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
  15.       GROUP BY SupportLogs.SupportStaff
  16.       PIVOT SupportLogs.Status)
This assumes the results of the crosstab are named Col2, Col3, ...
Jan 6 '09 #10

NeoPa
Expert Mod 15k+
P: 31,186
As you now say you're working within a report however, you should be able to have an (unbound) TextBox in the body section of your report with a simple formula in it referring to the bound TextBoxes used to dispaly the column figures.
Expand|Select|Wrap|Line Numbers
  1. =Nz([txtCol2],0)+Nz([txtCol3],0)+Nz([txtCol4],0)+...
You get the picture.

NB. Make sure you reference the TextBox controls and NOT the underlying fields (which will not be accessible unless bound to something).
Jan 6 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.