Tags: access, anotherfield, database, field, header, hrs, microsoft, mysql, nin, no5, oracle, report, sql, sum, summed, y10

Sum if Yes

On Database » Microsoft Access

4,213 words with 7 Comments; publish: Mon, 31 Dec 2007 00:29:00 GMT; (25093.75, « »)

I have a report that I need a field summed in the header if another

field = yes.

Hrs Yes/No

5 Y

10 N

8 Y

2 N

In the header I would like it to sum the hours where Yes/No field =

Yes.

so the total would be 13 in this example.

thanks,

KO

All Comments

Leave a comment...

  • 7 Comments
    • turtle wrote:

      > I have a report that I need a field summed in the header if another

      > field = yes.

      > Hrs Yes/No

      > 5 Y

      > 10 N

      > 8 Y

      > 2 N

      > In the header I would like it to sum the hours where Yes/No field =

      > Yes.

      > so the total would be 13 in this example.

      > thanks,

      > KO

      After renaming Yes/No to AnotherField and calling the table tblHours:

      SELECT Sum(Nz([Hrs])) AS theSum FROM tblHours WHERE AnotherField =

      True;

      gave theSum of 13. AnotherField is of type Yes/No.

      James A. Fortune

      #1; Mon, 31 Dec 2007 00:30:00 GMT
    • I would like to do this on the report not in the query if that is

      possible

      #2; Mon, 31 Dec 2007 00:31:00 GMT
    • turtle wrote:

      > I would like to do this on the report not in the query if that is

      > possible

      In the ControlSource for the textbox on the report:

      =GetValue("SELECT Sum(Nz([Hrs])) AS theSum FROM tblHours WHERE

      AnotherField = True;", "theSum")

      Then in a module:

      Public Function GetValue(strSQL As String, strField As String) As

      Double

      Dim MyDB As Database

      Dim MyRS As Recordset

      GetValue = 0

      Set MyDB = CurrentDb

      Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)

      If MyRS.RecordCount > 0 Then

      MyRS.MoveFirst

      GetValue = MyRS(strField)

      End If

      MyRS.Close

      Set MyRS = Nothing

      Set MyDB = Nothing

      End Function

      James A. Fortune

      #3; Mon, 31 Dec 2007 00:32:00 GMT
    • On 7 Mar 2005 10:05:25 -0800, turtle wrote:

      > I have a report that I need a field summed in the header if another

      > field = yes.

      > Hrs Yes/No

      > 5 Y

      > 10 N

      > 8 Y

      > 2 N

      > In the header I would like it to sum the hours where Yes/No field =

      > Yes.

      > so the total would be 13 in this example.

      > thanks,

      > KO

      In an unbound text control:

      =Sum(IIf([Yes/NoField]=-1,[Hrs],0))

      --

      Fred

      Please only reply to this newsgroup.

      I do not reply to personal email.

      #4; Mon, 31 Dec 2007 00:33:00 GMT
    • turtle wrote:

      > I have a report that I need a field summed in the header if another

      > field = yes.

      > Hrs Yes/No

      > 5 Y

      > 10 N

      > 8 Y

      > 2 N

      > In the header I would like it to sum the hours where Yes/No field =

      > Yes.

      > so the total would be 13 in this example.

      > thanks,

      > KO

      You might try this in a TextBox's ControlSource property:

      =Sum(IIf([Yes/No]="Y", Hrs, 0))

      --

      MGFoster:::mgf00 <at> earthlink <decimal-point> net

      Oakland, CA (USA)

      #5; Mon, 31 Dec 2007 00:34:00 GMT
    • MGFoster wrote:

      > turtle wrote:

      > > I have a report that I need a field summed in the header if another

      > > field = yes.

      > > Hrs Yes/No

      > > 5 Y

      > > 10 N

      > > 8 Y

      > > 2 N

      > > In the header I would like it to sum the hours where Yes/No field =

      > > Yes.

      > > so the total would be 13 in this example.

      > > thanks,

      > > KO

      > You might try this in a TextBox's ControlSource property:

      > =Sum(IIf([Yes/No]="Y", Hrs, 0))

      > --

      > MGFoster:::mgf00 <at> earthlink <decimal-point> net

      > Oakland, CA (USA)

      I tried not to assume that the report was based on tblHours, but if

      that's the case then simpler is better.

      James A. Fortune

      #6; Mon, 31 Dec 2007 00:35:00 GMT
    • fredg wrote:

      > On 7 Mar 2005 10:05:25 -0800, turtle wrote:

      >

      >>I have a report that I need a field summed in the header if another

      >>field = yes.

      >>

      >>Hrs Yes/No

      >>5 Y

      >>10 N

      >>8 Y

      >>2 N

      >>

      >>In the header I would like it to sum the hours where Yes/No field =

      >>Yes.

      >>

      >>so the total would be 13 in this example.

      >>thanks,

      >>KO

      >

      > In an unbound text control:

      > =Sum(IIf([Yes/NoField]=-1,[Hrs],0))

      or

      =Sum(Abs([Yes/NoField])*[Hrs])

      --

      This sig left intentionally blank

      #7; Mon, 31 Dec 2007 00:36:00 GMT