Summing A row of data in access

KingKen · #1 January 6th, 2009, 02:42 PM

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

 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

Expand|Select|Wrap|Line Numbers

 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 · #2 January 6th, 2009, 04:23 PM

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 · #3 January 6th, 2009, 04:33 PM

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 · #4 January 6th, 2009, 04:40 PM

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

 SELECT [Col1],

       [Col2],

       [Col3],

       [Col1]+[Col2]+[Col3] AS [Total]
 
FROM MyTable

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

KingKen · #5 January 6th, 2009, 05:04 PM

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

 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 · #6 January 6th, 2009, 05:23 PM

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 · #7 January 6th, 2009, 05:50 PM

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 · #8 January 6th, 2009, 06:10 PM

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

 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:

Expand|Select|Wrap|Line Numbers

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

that should get you your desired result.

KingKen · #9 January 6th, 2009, 07:08 PM

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

 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 · #10 January 6th, 2009, 08:25 PM

Expand|Select|Wrap|Line Numbers

 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 · #11 January 6th, 2009, 08:31 PM

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

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

Summing A row of data in access

Popular Articles

What is Bytes?