Sum 4 Fields with conditions

[Swim Dad]

Hello,
Forgive me as I am a beginner trying to do something complicated. Using
Access 2003
I need some guidance on what would be the best way to achieve the following:
I would like to sum 2 fields (with conditions) and display the results in
another field on a form. I am currently using an update query.

All fields are in a table called "Source"

Sum or Result field is called "Total_Amount".

"Amount" and "Amount_1" are the names of the fields I would like to add
together and display the total in the "Total_Amount" field. Default values
for both are $0.00

"Date" field (companion with Amount) and "Date_1" field (companion with
Amount_1) are the conditional fields that when populated (not null) it is
okay to add together the "Amount" and "Amount_1" values and populate in
"Total_Amount"

I currently have this in an update query:
UPDATE Source SET Source.Total_Amount=Sum(IIf([Amount]<>"$0.00" and [Date]
is not null, [Amount]) + (IIf([Amount_1]<>"$0.00" and [Date_1] is not null,
[Amount_1])))

This code does not do anything that I can tell. At least it is not
populating the Total_Amount field.

Thanls
Swim Dad

 

[Douglas J. Steele]

Calculated fields like that should not be stored in the table. As fellow MVP
John Vinson likes to say, "Storing calculated data generally accomplishes
only three things: it wastes disk space, it wastes time (a disk fetch is
much slower than almost any reasonable calculation), and it risks data
validity, since once it's stored in a table either the Total or one of the
fields that goes into the total may be changed, making the value WRONG."

Simply create a query that returns the values you want, and use the query
wherever you would otherwise have used the table.

 

[John Spencer]

First of all DON"T store calculated values except in very rare circumstances.
If you store the calculated value and aren't consistent about fixing it you
will end up with discrepancies in what is stored versus what is calculated.

You can use the following expression to add the two amounts together and
display then result. You can use the expression as a control's source or you
and use it in query as a calculated field.

IIF(IsNull([Date]) or IsNull([date1]) Or Amount=0 or Amount1=0,
Null,Amount+Amount1)

Also
Sum is used to total values in multiple rows of data. I think you just want
to add the two fields together.

You should test the amounts to see if they are not 0 unless they are string
fields when you would test for "$0.00". AND if they are string fields you are
going to end up concatenating them together instead of adding them.

$3.00 + $4.00 will give you as string like the following $3.00$4.00 and not
$7.00. If the fields are currency or number fields then you will get 7, BUT
you must test for number values and not string values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Swim Dad]

Thanks for the replies. I am close to the solution based on the guidance
below.

First I will need to store the calculated field. I accomplish this using an
update query which is run every hour. The database is not that large (600
records) so it does not eat many resources.

Clarifying my problem:
Amount and Amount_1 could both have numbers (currency). I would only like
them to be totaled and stored in Total_Amount IF the "Date" and / or Date_1
field(s) are populated.
Amount Date Amount_1 Date_1
Record 1 $2.00 06/30/2009 $4.00 Null

Total_Amount for record 1 would be $2.00 based on date field populated.
--
Swim Dad

First of all DON"T store calculated values except in very rare circumstances.
If you store the calculated value and aren't consistent about fixing it you
will end up with discrepancies in what is stored versus what is calculated.

You can use the following expression to add the two amounts together and
display then result. You can use the expression as a control's source or you
and use it in query as a calculated field.

IIF(IsNull([Date]) or IsNull([date1]) Or Amount=0 or Amount1=0,
Null,Amount+Amount1)

Also
Sum is used to total values in multiple rows of data. I think you just want
to add the two fields together.

You should test the amounts to see if they are not 0 unless they are string
fields when you would test for "$0.00". AND if they are string fields you are
going to end up concatenating them together instead of adding them.

$3.00 + $4.00 will give you as string like the following $3.00$4.00 and not
$7.00. If the fields are currency or number fields then you will get 7, BUT
you must test for number values and not string values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,
Forgive me as I am a beginner trying to do something complicated. Using
Access 2003
I need some guidance on what would be the best way to achieve the following:
I would like to sum 2 fields (with conditions) and display the results in
another field on a form. I am currently using an update query.

All fields are in a table called "Source"

Sum or Result field is called "Total_Amount".

"Amount" and "Amount_1" are the names of the fields I would like to add
together and display the total in the "Total_Amount" field. Default values
for both are $0.00

"Date" field (companion with Amount) and "Date_1" field (companion with
Amount_1) are the conditional fields that when populated (not null) it is
okay to add together the "Amount" and "Amount_1" values and populate in
"Total_Amount"

I currently have this in an update query:
UPDATE Source SET Source.Total_Amount=Sum(IIf([Amount]<>"$0.00" and [Date]
is not null, [Amount]) + (IIf([Amount_1]<>"$0.00" and [Date_1] is not null,
[Amount_1])))

This code does not do anything that I can tell. At least it is not
populating the Total_Amount field.

Thanls
Swim Dad

 

[Douglas J. Steele]

You do NOT need to store the values.

Your query could be something like:

SELECT [Amount], [Date], [Amount_1], [Date_1],
CCur([Amount]) + IIf(Nz([Date], 0) = Nz([Date_1], 0), CCur([Amount_1]), 0)
AS Total
FROM MyTable

Incidentally, you should rename the field named Date. Date is a reserved
word, and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the replies. I am close to the solution based on the guidance
below.

First I will need to store the calculated field. I accomplish this using
an
update query which is run every hour. The database is not that large (600
records) so it does not eat many resources.

Clarifying my problem:
Amount and Amount_1 could both have numbers (currency). I would only like
them to be totaled and stored in Total_Amount IF the "Date" and / or
Date_1
field(s) are populated.
Amount Date Amount_1 Date_1
Record 1 $2.00 06/30/2009 $4.00 Null

Total_Amount for record 1 would be $2.00 based on date field populated.
--
Swim Dad

First of all DON"T store calculated values except in very rare
circumstances.
If you store the calculated value and aren't consistent about fixing it
you
will end up with discrepancies in what is stored versus what is
calculated.

You can use the following expression to add the two amounts together and
display then result. You can use the expression as a control's source or
you
and use it in query as a calculated field.

IIF(IsNull([Date]) or IsNull([date1]) Or Amount=0 or Amount1=0,
Null,Amount+Amount1)

Also
Sum is used to total values in multiple rows of data. I think you just
want
to add the two fields together.

You should test the amounts to see if they are not 0 unless they are
string
fields when you would test for "$0.00". AND if they are string fields
you are
going to end up concatenating them together instead of adding them.

$3.00 + $4.00 will give you as string like the following $3.00$4.00 and
not
$7.00. If the fields are currency or number fields then you will get 7,
BUT
you must test for number values and not string values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,
Forgive me as I am a beginner trying to do something complicated.
Using
Access 2003
I need some guidance on what would be the best way to achieve the
following:
I would like to sum 2 fields (with conditions) and display the results
in
another field on a form. I am currently using an update query.

All fields are in a table called "Source"

Sum or Result field is called "Total_Amount".

"Amount" and "Amount_1" are the names of the fields I would like to add
together and display the total in the "Total_Amount" field. Default
values
for both are $0.00

"Date" field (companion with Amount) and "Date_1" field (companion with
Amount_1) are the conditional fields that when populated (not null) it
is
okay to add together the "Amount" and "Amount_1" values and populate in
"Total_Amount"

I currently have this in an update query:
UPDATE Source SET Source.Total_Amount=Sum(IIf([Amount]<>"$0.00" and
[Date]
is not null, [Amount]) + (IIf([Amount_1]<>"$0.00" and [Date_1] is not
null,
[Amount_1])))

This code does not do anything that I can tell. At least it is not
populating the Total_Amount field.

Thanls
Swim Dad