subform and SUM

[David Grist]

Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description field
= "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[Tom van Stiphout]

On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist" <di*****@verizon.net>
wrote:

Check out DSum in the help file.
-Tom.

>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description field
= "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[David Grist]

I checked out dsum but that is summing the whole table.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:r8********************************@4ax.com...

On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist" <di*****@verizon.net>
wrote:

Check out DSum in the help file.
-Tom.

>>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field
= "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[tina]

try

=Sum(IIf([description]="tour installment",[amounttend],0)

hth
"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain

criteria.

>
It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description

field

= "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[Tom van Stiphout]

On Sat, 03 Nov 2007 04:43:01 GMT, "David Grist" <di*****@verizon.net>
wrote:

Not if you put in a where clause as the 3rd argument:
=dsum("AmountEnd", "SomeTable", "Description='tour installment'")

-Tom.

>I checked out dsum but that is summing the whole table.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:r8********************************@4ax.com.. .

>On Sat, 03 Nov 2007 01:54:45 GMT, "David Grist" <di*****@verizon.net>
wrote:

Check out DSum in the help file.
-Tom.

>>>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field
= "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[David Grist]

Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[Tom van Stiphout]

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[David Grist]

Yes, the description was a number not text. Sorry for the bit of confusion.
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

>>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[David Grist]

Tom,

This is what I now have and it's still totalling all receipts in a records
of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and where the
description is equal to 2.

Can you offer any suggestions?
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

>>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[Salad]

David Grist wrote:

Tom,

This is what I now have and it's still totalling all receipts in a records
of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and where the
description is equal to 2.

Can you offer any suggestions?

Maybe build a Totals query that breaks/groups on StudentID or whatever.
Then in the subform use that query and link the StudentID to StudentID
in the main form.

>
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

>>On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>>>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[tina]

if the subform is only showing records for the "current" student, then the
expression i gave you should work. where is the control that you want to
show the total?

hth
"David Grist" <di*****@verizon.netwrote in message
news:TJ4Xi.188$FO.59@trndny01...

Tom,

This is what I now have and it's still totalling all receipts in a records
of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and where the
description is equal to 2.

Can you offer any suggestions?
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...
Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[Bob Quintal]

"David Grist" <di*****@verizon.netwrote in
news:TJ4Xi.188$FO.59@trndny01:

Tom,

This is what I now have and it's still totalling all receipts in a
records of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and
where the description is equal to 2.

Can you offer any suggestions?

The quotation marks around your criteria are misplaced. Try

=DSum("[amounttend]","[receipts]","[StudentID]= " & Forms![Student
Account Data].[StudentID] & " And [description]=2")

Where studentID is a number or .

=DSum("[amounttend]","[receipts]","[StudentID]= """ & Forms![Student
Account Data].[StudentID] & """ And [description]=2")

if it is a text field.

>
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

>On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist"
<di*****@verizon.netwrote:

Of course you can have an AND condition. Any legal WHERE clause
is allowed.
Curious that earlier "Description" was a text field, whereas now
it appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>>>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table
and not just the membership record I'm working on. If there was
a way to put an "AND" argument after the description qualifier,
that might work? For instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...
Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a
certain criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the
description field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David Grist]

Tina,

This worked perfectly, as a matter of fact I created fields for all the
remaining charges and can now display them as well.

Thanks for your help!

"tina" <no****@address.comwrote in message
news:op********************@bgtnsc05-news.ops.worldnet.att.net...

if the subform is only showing records for the "current" student, then the
expression i gave you should work. where is the control that you want to
show the total?

hth
"David Grist" <di*****@verizon.netwrote in message
news:TJ4Xi.188$FO.59@trndny01...

>Tom,

This is what I now have and it's still totalling all receipts in a
records
of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and where the
description is equal to 2.

Can you offer any suggestions?
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com.. .

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.
Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and
not
just the membership record I'm working on. If there was a way to put
an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...
Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[David Grist]

Tom,

Although your idea was the same as mine, it kept totalling the entire table
instead of the single record, but, another user was able to fix my issue.
Thanks for all your efforts!

- Dave

"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...

On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.

>>Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and not
just the membership record I'm working on. If there was a way to put an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...

>>Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the description
field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave

[tina]

you're welcome :)
"David Grist" <di*****@verizon.netwrote in message
news:3s7Xi.920$lx.211@trndny05...

Tina,

This worked perfectly, as a matter of fact I created fields for all the
remaining charges and can now display them as well.

Thanks for your help!

"tina" <no****@address.comwrote in message
news:op********************@bgtnsc05-news.ops.worldnet.att.net...

if the subform is only showing records for the "current" student, then

the

expression i gave you should work. where is the control that you want to
show the total?

hth
"David Grist" <di*****@verizon.netwrote in message
news:TJ4Xi.188$FO.59@trndny01...

Tom,

This is what I now have and it's still totalling all receipts in a
records
of the subform.

=DSum("[amounttend]","[receipts]","Forms![Student Account
Data].[StudentID]=[StudentID]" And "[description]=2")

I want just the total of AMOUNTTEND for the current student and where

the

description is equal to 2.

Can you offer any suggestions?
"Tom van Stiphout" <no*************@cox.netwrote in message
news:8e********************************@4ax.com...
On Sat, 03 Nov 2007 19:31:52 GMT, "David Grist" <di*****@verizon.net>
wrote:

Of course you can have an AND condition. Any legal WHERE clause is
allowed.
Curious that earlier "Description" was a text field, whereas now it
appears a numeric field.
=dsum("[amounttend]","[receipts]","[description]=2 AND
[studentid]=" & forms![student account data].studentid)

-Tom.


Unfortunately, none of the offered solutions are working for me

I tried, on the subform, ....

=dsum("[amounttend]","[receipts]","[description]=2")

this sums ALL records meeting the criteria in the receipts table and
not
just the membership record I'm working on. If there was a way to put
an
"AND" argument after the description qualifier, that might work? For
instance...

=dsum("[amounttend]","[receipts]","[description]=2" AND
"[studentid]=forms![student account data].studentid)

that might work.... is there a way?

"David Grist" <di*****@verizon.netwrote in message
news:FxQWi.71$Cc.56@trndny09...
Hello,

Any help would be appreciated!!!

I need to sum AMOUNTTEND on a receipts subform that meet a certain
criteria.

It has to be something like.

=sum([amounttend]) where [description]="tour installment"

in other words, I want to total only the amounts when the

description

>field = "tour installment"

Can anyone help with the correct syntax?

Thanks,

Dave