Sum values based on multiple criteria

[jseverado]

If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?

example

Tbl 1
PK, Month
Percent

Tbl 2
FK, Month Enrolled
Qty of Rx in the 1st month enrolled
Qty of Rx in 2nd qtr
Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)

If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent

Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.
I only know how to create Query's using the design mode. I dont know how to write SQL statements.

I suspect it's a flaw in my DB design, just need someone to help me work it out.
thank you for any help,
Jen

 

[Guus2005]

You question raises even more questions. Yes there may be a flaw in your DB design but i am unable to tell from the information you give.

how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.

What field do you mean? Which field must be Null for you to sum another field?

My suggestion will be that you use the Nz() function to convert Null values to a default value.

HTH