Sum query based on another field

[anon]

I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record where the description is =
"member"


any ideas?

 

[Duane Hookom]

Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )

 

[MDunn]

Build a total query for your report with the following:

Decription: Total=GroupBy Sort=Ascending(optional)
Quantity: Total=Sum


Build the report (use Wizard AutoReport: Tabular) if you
like:


In the Detail Section build an "On Format" [Event
Procedure] with the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
If Decription = "member" Then
SumOfQuantity.Visible = True
Else
SumOfQuantity.Visible = False
End If
End Sub


Let me know if it worked out the way you wanted.

MRDunn

 

[anon]

This only counts the number of times that a record with 'member' come up.
I need to know the total of the quantity field every time the description
field is 'member'
e.g.

Description Quantity
member 2
nonmember 3
nonmember 22
member 44

this would give a total of 46.

Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )

--
Duane Hookom
MS Access MVP
--

I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record where the description

is

=
"member"


any ideas?

 

[Fons Ponsioen]

Try:
=Sum(IIF([Description]="member",Quantity,0 ) )
Hope this helps.
Fons

-----Original Message-----
This only counts the number of times that a record with 'member' come up.
I need to know the total of the quantity field every time the description
field is 'member'
e.g.

Description Quantity
member 2
nonmember 3
nonmember 22
member 44

this would give a total of 46.

Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )

--
Duane Hookom
MS Access MVP
--

I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record

where the description
is

=

.

 

[Duane Hookom]

=Sum( Abs([Description]="member") * [Quantity])

--
Duane Hookom
MS Access MVP
--

This only counts the number of times that a record with 'member' come up.
I need to know the total of the quantity field every time the description
field is 'member'
e.g.

Description Quantity
member 2
nonmember 3
nonmember 22
member 44

this would give a total of 46.

Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )

--
Duane Hookom
MS Access MVP
--

I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record where the description

is

=
"member"


any ideas?

 

[anon]

Thanks....worked a treat.

Try:
=Sum(IIF([Description]="member",Quantity,0 ) )
Hope this helps.
Fons

-----Original Message-----
This only counts the number of times that a record with 'member' come up.
I need to know the total of the quantity field every time the description
field is 'member'
e.g.

Description Quantity
member 2
nonmember 3
nonmember 22
member 44

this would give a total of 46.

Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )

--
Duane Hookom
MS Access MVP
--

I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record

where the description
is

=
"member"


any ideas?

.