SUM'ing fields depending on IF a cetain value exists in another?

[NewShoes]

Hey all,

I am quickly learning lots about Access but this one has me a little stumped. I need to SUM, say 2 or 3 fields in a query but only if there is a value present in another field (on another table). How difficult is this to do?

I am assuming I need to use the IIF function?

I would like to do this in a query (QBE) and avoid VBA for now if it's possible?

Many thanks,
-NS

 

[MSAccessRookie]

You are on the right track, except that the SUM() Function will not work here. You could use an IIf() statement that tested the value of the Field in the other table and returned the numeric sum (a + b + c...) for the columns to add if the test was true, and another value to show that the test failed.

Is this what you are looking for?

Note: No VBA would be required for this solution.

 

[NewShoes]

Thanks for your reply. I;m not sure if that is what I am looking (I possibly used SUM incorrectly).

If I can explain in simple terms...

Price: IIF fieldC is >0 Then (fieldA + fieldC) Else (fieldA + fieldB).

Can I put something like that into a query that Access will understand?

Thanks,
-NS

 

[MSAccessRookie]

Thanks for your reply. I;m not sure if that is what I am looking (I possibly used SUM incorrectly).

If I can explain in simple terms...

Price: IIF fieldC is >0 Then (fieldA + fieldC) Else (fieldA + fieldB).

Can I put something like that into a query that Access will understand?

Thanks,
-NS

When you say Field, are you referring to a Field that is on a Form, or to a Column that is in a Table? Generally, the word Field is used to refer to a Control located on a Form.

 

[SOS]

Generally, the word Field is used to refer to a Control located on a Form.

That would be an erroneous use of the term then. A FIELD is something from a table or Query. A CONTROL is something from a form or report.

 

[MSAccessRookie]

That would be an erroneous use of the term then. A FIELD is something from a table or Query. A CONTROL is something from a form or report.

I have always been led to believe it was the way that I described it. My point was not to define what was right or wrong, only to see what the intent of the OP was. Once we have the understanding of the OP, we can help out better.

 

[NewShoes]

Thanks. WHen I say field I mean a "cell" on a query or table..if that makes sense.

 

[marlan]

Price: IIF fieldC is >0 Then (fieldA + fieldC) Else (fieldA + fieldB).

You can put the IIf in a sum function as part of a query:

Sum (IIF (fieldC > 0, (fieldA + fieldC), (fieldA + fieldB)))

 

[NewShoes]

You can put the IIf in a sum function as part of a query:

Sum (IIF (fieldC > 0, (fieldA + fieldC), (fieldA + fieldB)))

Excellent, many thanks!

 

[Brianwarnock]

So you are summing the sum of the fields over the table,not the 2 fields per record?

Brian

 

[NewShoes]

Thanks for all your replies on this! I have sorted the original issue. However, I now have a further question. I have created a derived field (Total) but down I would like to create a second derived field that uses the other derived field. I have got this to work but each time the query is ran, it asks for Total. If I click ok (without entering anything) it works as it should.

Any help would be great!
-NS

 

[marlan]

I found that if you want to be soure it works, you save the first Query (qr1), giving each calculated field an alias (as you'v done, calling it Total). An other query (qr2) can then select form qr1, and calculate it's fields.
if you want it to look like one query, just begin qr2 with '*':

SELECT * ,[calculated_fl1] as fildName1,[calculated_fl2] as fildName2 ... 
FROM qr1

 

[Brianwarnock]

You could quote the formula that gives the derived field Total instead of using the alias, but that is not usually necessary. Posting your SQL may shed some light on this.

Brian