Summing A row of data in access

[KingKen]

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:

Col 1       col2    col3   total
  23           10      1        34
   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

Dim gbl As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
gbl = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0)
Col7 = glb
End Sub

Is there a way to get this done? there must be. Help!

[NeoPa]

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?

[KingKen]

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?

[NeoPa]

OK.

In the absence of your detailed SQL I will try to put something down of a general nature that you can build from :

SELECT [Col1],
       [Col2],
       [Col3],
       [Col1]+[Col2]+[Col3] AS [Total]

FROM MyTable

Line #4 illustrates the basic concept of what you need to do.

[KingKen]

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.

PARAMETERS [forms]![frmSearch]![Begdate] DateTime, [forms]![frmSearch]![Enddate] DateTime;
TRANSFORM Count(SupportLogs.Status) AS CountOfStatus
SELECT SupportLogs.SupportStaff
FROM SupportLogs
WHERE (((SupportLogs.DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
GROUP BY SupportLogs.SupportStaff
PIVOT SupportLogs.Status;

[NeoPa]

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.

[KingKen]

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.

[tyrdrannoy]

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:

public function ReturnVal (col2, col3, col4, col5, col6) as long
Dim gbl As Integer 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) 
gbl = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0) 
ReturnVal = glb 
End function

then in your textbox data value:

=ReturnVal(txtCol2, txtCol3, txtCol4, txtCol5, txtCol6)

that should get you your desired result.

[KingKen]

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

Here is the working code

Public Function ReturnVal(txCol2, txCol3, txCol4, txCol5, txCol6) As Long
    Dim gbl As Long
    glb = Nz(Col2, 0) + Nz(Col3, 0) + Nz(Col4, 0) + Nz(Col5, 0) + Nz(Col6, 0)
    ReturnVal = glb
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

[NeoPa]

PARAMETERS [forms]![frmSearch]![Begdate] DateTime,
           [forms]![frmSearch]![Enddate] DateTime;

SELECT *,
       (Nz([Col2],0)
       +Nz([Col3],0)
       +Nz([Col4],0)
       +Nz([Col5],0)
       +Nz([Col6],0)) AS Total

FROM (TRANSFORM Count(SupportLogs.Status) AS CountOfStatus
      SELECT SupportLogs.SupportStaff
      FROM SupportLogs
      WHERE (((SupportLogs.DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
      GROUP BY SupportLogs.SupportStaff
      PIVOT SupportLogs.Status)

This assumes the results of the crosstab are named Col2, Col3, ...

[NeoPa]

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.

=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).