Sum query based on another field

  • Thread starter Thread starter anon
  • Start date Start date
A

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?
 
Add a text box to a group or report footer with a control source of:
=Sum( Abs([Description]="member") )
 
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
 
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.




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

--
Duane Hookom
MS Access MVP
--

anon said:
I have 2 fields in my report.

description
quantity

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


any ideas?
Click to expand...
Click to expand...
 
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
--

anon said:
I have 2 fields in my report.

description
quantity

I wish to sum the quantity field for each record
Click to expand...
Click to expand...
where the description
is
=
Click to expand...


.
Click to expand...
 
=Sum( Abs([Description]="member") * [Quantity])

--
Duane Hookom
MS Access MVP
--

anon said:
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.




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

--
Duane Hookom
MS Access MVP
--

anon said:
I have 2 fields in my report.

description
quantity

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


any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Thanks....worked a treat.


Fons Ponsioen said:
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
Click to expand...
where the description
is
=
"member"


any ideas?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top