select sum question

[cristip3001]

Hi, I'm a newbie in Access and SQL. I am trying to write a query in Access that returns the sum of values on a column. The query works fine, the only problem is that if there are no values it return empty. I would like to return value zero in this situation.

My dummy DummyTable table has two columns:
1. Price
2. Date

My query looks like:

SELECT SUM(DummyTable.Price)  AS TotalSum,
FROM DummyTable
WHERE DummyTable.Date>=StartingDate 
AND DummyTable.Date<=EndingDate;
 

StartingDate and EndingDate are parameters required when to run this query.


Thank you very much for any suggestions,
Cristian

[Scott Price]

Hi Cristian,

(Deleted original post to correct)

Make sure the default value of your Price field is set to 0.

You will need to rename your field Date to something else, as Date is a reserved word in Access.

Use the Dsum() function instead of Sum(). The Dsum() function has two required arguments of expr and domain. Look it up in M$ helpfile for the specifics.

I used this in my test db, and it returned the 0 figure desired.

If this doesn't work for you please post the sql code you are using.

Regards,
Scott

[Scott Price]

Here is also an excellent basic sql syntax tutorial:

Basic SQL syntax

Regards,
Scott

[cristip3001]

Hi Scott,

thank you very much for the reply. I've tried the following SQL querry in the SQL view:

DSum("Price", "DummyTable", "MyDate>=StartingDate AND MyDate<=EndingDate");
 

but got an error message:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'".

For the original query:

SELECT SUM(DummyTable.Price)  AS TotalSum,
FROM DummyTable
WHERE DummyTable.MyDate>=StartingDate
AND DummyTable.MyDate<=EndingDate;
 

I have data for the date between 1/1/2001 and 1/1/2007 for example; but not for 1/1/2100 and 1/1/2200; even thou it's some futuristic date I would still like to display zero value as the result of the query if there's no data. The "Price" field has the 0 value as default defined in the DummyTable.

Cheers,
Cris

[Scott Price]

Your syntax is part of the problem...

SELECT DSum([MyPrice],"tblDummy") AS TotalPrice, tblDummy.MyDate, tblDummy.DummyID
FROM tblDummy
WHERE (((tblDummy.MyDate)>Date()));

Is the syntax I used in my test db.

However, for cases where there are NO matches whatsoever, the query will still return a null value. Let me research the way around that, and will be back to you promptly :-)

Regards,
Scott

[Scott Price]

Ok, as far as I can tell, there is no way to coerce a query that returns null to 'show' a value of 0 instead without inserting a record that corresponds to your WHERE criteria. If that isn't an option, one workaround involves placing an invisible text box exactly placed over the visible control on your form or report that you are using to interact/display the query results. Then place in the OnOpen event of the form/report, the code that checks for a null value:

i.e. :

If IsNull(Me.TotalPrice) Then

Then make the TotalPrice control invisible, and the txtTotalPrice visible by using their respective .visible properties. In VB code this looks like this:

Me.TotalPrice.Visible = False ' Makes this control invisible
Me.txtTotalPrice.Visible = True ' Makes this control visible

Next set the value of the txtTotalPrice to "0$" or whatever you want it to be, by using this line of code:

Me.txtTotalPrice = "0$"

End you if statement (syntax is: End If), compile your code (go to Debug menu in VBEditor window, and click first option), save your changes and test your form open...

Let me know if this workaround is something along the lines of what you're looking for.

Regards,
Scott

[cristip3001]

Hi Scott,

thank you again for the prompt reply. What happens if I have something like:

SELECT Sum1, Sum2, Sum3, (Sum1+Sum2+Sum3) AS SumTotal
FROM
   (SELECT SUM(DummyTable1.Price) AS Sum1,
       FROM DummyTable1
           WHERE DummyTable1.MyDate>=StartingDate
           AND DummyTable1.MyDate<=EndingDate
   ),
 
   (SELECT SUM(DummyTable2.Price) AS Sum2,
       FROM DummyTable2
           WHERE DummyTable2.MyDate>=StartingDate
           AND DummyTable2.MyDate<=EndingDate
   ),
 
   (SELECT SUM(DummyTable3.Price) AS Sum3,
       FROM DummyTable3
           WHERE DummyTable3.MyDate>=StartingDate
           AND DummyTable3.MyDate<=EndingDate
   );
 

Lets say for DummyTable1 and 2 we have values in that Date interval, but not for DummyTable3. SumTotal will be displayed as Null value... Is there no way to display:


Sum1____Sum2____Sum3____SumTotal
-------------------------------------------------------------
value1____value2_____0______value1+value2+0

?

Regards,
Cris

[Scott Price]

Could you repost the sql code of your query, enclosing it first in the code tags (on the top of your reply window look for the # sign. Highlight your sql text and then click the button. Then add =sql, in the first [code] tag, ending up like this [code=sql]?) This makes it easier to read and understand.

Also, is this one query, or three?

Regards,
Scott

[Scott Price]

You can use the iif function to return a 0 value if the value of the query is null in the following manner: Place this statement in the criteria of your MyPrice field.

IIf(IsNull([tblDummy].[MyPrice]),0,[tblDummy].[MyPrice])

(IIF means If and Only If. It has three required arguments: expression, truevalue and falsevalue. It evaluates the expression as true or false, then returns whichever value you provide...)

The problem is that if you are trying to perform a Sum or DSum calculation on the same field at the same time as you use the IIF statement as criteria, you get an error message.

To work around this, you can create three separate queries, then combine them in a fourth query.

I.e. query1 =

SELECT tblDummy.MyPrice, tblDummy.MyDate, tblDummy.DummyID
FROM tblDummy
WHERE (((tblDummy.MyPrice)=IIf(IsNull([tblDummy].[MyPrice]),0,[tblDummy].[MyPrice])) AND ((tblDummy.MyDate)<Date()));

query2 is the same for dummy table 2, etc for dummy table 3.

In query 4 you place your Sum or DSum statements.

Let me know if this isn't clearer than mud :-)

Regards,
Scott

[cristip3001]

First I'll repost the query:

SELECT Sum1, Sum2, Sum3, (Sum1+Sum2+Sum3) AS SumTotal
FROM
(SELECT SUM(DummyTable1.Price) AS Sum1,
FROM DummyTable1
WHERE DummyTable1.MyDate>=StartingDate
AND DummyTable1.MyDate<=EndingDate
),
 
(SELECT SUM(DummyTable2.Price) AS Sum2
FROM DummyTable2
WHERE DummyTable2.MyDate>=StartingDate
AND DummyTable2.MyDate<=EndingDate
),
 
(SELECT SUM(DummyTable3.Price) AS Sum3
FROM DummyTable3
WHERE DummyTable3.MyDate>=StartingDate
AND DummyTable3.MyDate<=EndingDate
);
 

[cristip3001]

Hi Scott,

I was trying the above query. For the interior query, it returns 0 as expected, but when I try to wrap it in a new query to sum it, it still displays Null value.

I can't figure it out where is the issue because if I try to sum a list of zeros...it should return 0 right?

Regards,
Cris

SELECT Sum(IntermediateSums1)  AS TotalSum1
FROM
(
    SELECT DummyTable1.Price AS IntermediateSums1
        FROM DummyTable1
        WHERE 
        (
            (
                (DummyTable1.Price)=
                IIf(
                    IsNull(DummyTable1.Price),
                    0,    
                    DummyTable1.Price
                )
            )
            AND
            (DummyTable1.MyDate>=StartingDate) 
            AND
             (DummyTable1.MyDate<=EndingDate)
        )
)
;
 
 
 
 

[Scott Price]

Is this a homework question or are you working on an actual database?

If an actual database, please give me some more details about what data you are actually working with and what you are now trying to accomplish, including the answers to the following questions: Where are you hoping to display this data? How are you wanting to interact with it?

For your question in your 3rd post, what you are trying to accomplish there is much easier done (and more appropriately and efficiently) on a report.

Queries consume much more computer resources, thus resulting in performance issues. Since the IIF statement DOES allow you to display the desired 0$ figure, on a report you can then add a calculated control that totals each of the other sums.

Regards,
Scott

[cristip3001]

Hi Scott,

I am working on an app that is linked to an Access db. I have three tables:

1. TableExpenses1 with a field DateExpenses1, CostExpenses1, ...
2. TableExpenses2 with a field DateExpenses2, CostExpenses2, ...
3. TableExpenses3 with a field DateExpenses3, CostExpenses3, ...

I need to generate a report in Access like this:

Between Starting Date X and Ending Date Y there are the following Expenses:
for Expenses 1 we have E1 dollars
for Expenses 2 we have E2 dollars
for Expenses 3 we have E3 dollars

Total: E1+E2+E3 dollars

That's why I was looking to generate the query from the previous messages.

I've also tried the following query, but still doesn't display any zero value as result when there is no data in that date interval:

SELECT TotalSumForExpenses1
FROM 
(
    SELECT Sum(IntermediateSums)  AS TotalSumForExpenses1
    FROM
    (
        SELECT DummyTable1.Price AS IntermediateSums
            FROM DummyTable1
            WHERE 
            (
                (
                    (DummyTable1.Price)=
                    IIf(
                        IsNull(DummyTable1.Price),
                        0,    
                        DummyTable1.Price
                    )
                )
                AND
                (DummyTable1.MyDate>=StartingDate) 
                AND
                (DummyTable1.MyDate<=EndingDate)
            )
    )
)
WHERE
(TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
;
 

Regards,
Cristian

[Scott Price]

Hi Scott,

I am working on an app that is linked to an Access db. I have three tables:

1. TableExpenses1 with a field DateExpenses1, CostExpenses1, ...
2. TableExpenses2 with a field DateExpenses2, CostExpenses2, ...
3. TableExpenses3 with a field DateExpenses3, CostExpenses3, ...

I need to generate a report in Access like this:

Between Starting Date X and Ending Date Y there are the following Expenses:
for Expenses 1 we have E1 dollars
for Expenses 2 we have E2 dollars
for Expenses 3 we have E3 dollars

Total: E1+E2+E3 dollars

That's why I was looking to generate the query from the previous messages.

I've also tried the following query, but still doesn't display any zero value as result when there is no data in that date interval:

SELECT TotalSumForExpenses1
FROM 
(
    SELECT Sum(IntermediateSums)  AS TotalSumForExpenses1
    FROM
    (
        SELECT DummyTable1.Price AS IntermediateSums
            FROM DummyTable1
            WHERE 
            (
                (
                    (DummyTable1.Price)=
                    IIf(
                        IsNull(DummyTable1.Price),
                        0,    
                        DummyTable1.Price
                    )
                )
                AND
                (DummyTable1.MyDate>=StartingDate) 
                AND
                (DummyTable1.MyDate<=EndingDate)
            )
    )
)
WHERE
(TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
;
 

Regards,
Cristian

Hi Cristian,

Apologies for the homework question... I was getting worried with all the talk about dummy tables :S.

I'm drawing a blank on how to use sql in how to do what you are asking about... Using the setup we have already discussed (i.e. the IIF statement returning the 0$ figure) I can in my test db display the 0$ in a form, but not in a report.

The way I would work around it (if it were me) is to code this into vba. If you are interested in that method, I can work up a quick and dirty code method that will accomplish what you're looking for. Let me know...

I'll keep poking around meantime, and see if there's something I'm overlooking.

Regards,
Scott

[cristip3001]

Hi Scott,

that's ok. I'm very happy I can discuss with someone this issue. Unfortunately I've spent already 2 days struggling and destroying my brains using with only sql statements to solve the problem. Unfortunately, again, I didn't find any liable solution on the internet, and that's the first motive I've joined this network.

Unfortunately, again, I'm zero in VB so I would like first to try an sql solution. My query as you saw was composed of three sub queries:
I'm getting the sum from all the three tables and I display the generated sum and the sum of all the sums :)

If you find anything that solves this issue using pure sql please let me know.

Regards and many thanks,

Cristian

[cristip3001]

and my big query is:

 
SELECT TotalSumForExpenses1, TotalSumForExpenses2, TotalSumForExpenses3, TotalSumForExpenses1+TotalSumForExpenses2+TotalSumForExpenses3 AS TotalSumExpenses, StartingDate, EndingDate
 
FROM
(
SELECT TotalSumForExpenses1
FROM 
(
    SELECT Sum(IntermediateSums1)  AS TotalSumForExpenses1
    FROM
    (
        SELECT TableExpenses1.Price AS IntermediateSums1
            FROM TableExpenses1
            WHERE 
            (
                (
                    (TableExpenses1.Price)=
                    IIf(
                        IsNull(TableExpenses1.Price),
                        0,    
                        TableExpenses1.Price
                    )
                )
                AND
                (TableExpenses1.MyDate>=StartingDate) 
                AND
                (TableExpenses1.MyDate<=EndingDate)
            )
    )
)
WHERE
(TotalSumForExpenses1=IIf(IsNull(TotalSumForExpenses1),0,TotalSumForExpenses1))
),
 
 
(
SELECT TotalSumForExpenses2
FROM 
(
    SELECT Sum(IntermediateSums2)  AS TotalSumForExpenses2
    FROM
    (
        SELECT TableExpenses2.Price AS IntermediateSums2
            FROM TableExpenses2
            WHERE 
            (
                (
                    (TableExpenses2.Price)=
                    IIf(
                        IsNull(TableExpenses2.Price),
                        0,    
                        TableExpenses2.Price
                    )
                )
                AND
                (TableExpenses2.MyDate>=StartingDate) 
                AND
                (TableExpenses2.MyDate<=EndingDate)
            )
    )
)
WHERE
(TotalSumForExpenses2=IIf(IsNull(TotalSumForExpenses2),0,TotalSumForExpenses2))
),
 
 
(
SELECT TotalSumForExpenses3
FROM 
(
    SELECT Sum(IntermediateSums3)  AS TotalSumForExpenses3
    FROM
    (
        SELECT TableExpenses3.Price AS IntermediateSums3
            FROM TableExpenses3
            WHERE 
            (
                (
                    (TableExpenses3.Price)=
                    IIf(
                        IsNull(TableExpenses3.Price),
                        0,    
                        TableExpenses3.Price
                    )
                )
                AND
                (TableExpenses3.MyDate>=StartingDate) 
                AND
                (TableExpenses3.MyDate<=EndingDate)
            )
    )
)
WHERE
(TotalSumForExpenses3=IIf(IsNull(TotalSumForExpenses3),0,TotalSumForExpenses3))
)
;
 

[Scott Price]

Hi Cristian,

Glad you're not getting discouraged :-) This is what I've come up with... It's needing a total of 4 queries to get the job done, but I think it's along the lines of what you want.

qryExpense1:

SELECT Sum(tblExpense1.MyPrice) AS TotalExpenses, tblExpense1.MyDate
FROM tblExpense1
GROUP BY tblExpense1.MyDate
HAVING (((tblExpense1.MyDate) Between #6/7/2000# And #6/7/2010#));

qryExpense2:

SELECT Sum(tblExpense2.MyPrice) AS TotalExpenses, tblExpense2.MyDate
FROM tblExpense2
GROUP BY tblExpense2.MyDate
HAVING (((tblExpense2.MyDate) Between #6/7/2000# And #6/7/2010#));

qryExpense3:

SELECT Sum(tblExpense1.MyPrice) AS TotalExpenses, tblExpense1.MyDate
FROM tblExpense1
GROUP BY tblExpense1.MyDate
HAVING (((tblExpense1.MyDate) Between #6/7/2000# And #6/7/2010#));

rqryExpenses:

SELECT TotalExpenses, MyDate From qryExpense1
Union
Select TotalExpenses, MyDate From qryExpense2
UNION Select TotalExpenses, MyDate From qryExpense3;

Now in design view of your report called something like rptExpenses, place a text box in the Report Footer section called Grand & Glorious Total Expenses (or whatever you want it to be...) in this text box write the following: =Sum([TotalExpenses])

This SHOULD be along the lines of what you want. Hope so, anyway :-)

Kind regards,
Scott

[cristip3001]

Hi Scott,

I got in a MS ACCESS book a solution like:
Nz([YourFieldName or expression],0)

SELECT Nz(Sum(IntermediateSums3),0) AS TotalSumForExpenses3

which returns 0 if Sum(IntermediateSum3) is NULL.

Thank you very much for not letting me loosing my brains,

Cris

[Scott Price]

Hi Scott,

I got in a MS ACCESS book a solution like:
Nz([YourFieldName or expression],0)

SELECT Nz(Sum(IntermediateSums3),0) AS TotalSumForExpenses3

which returns 0 if Sum(IntermediateSum3) is NULL.

Thank you very much for not letting me loosing my brains,

Cris

Glad you got something workable figured out! I was aware of the Nz() function, and tested it first thing, but must have made my syntax wrong because it didn't work ;-(

Anyway, good luck on your app.

Regards,
Scott

[cristip3001]

Hi Scott,

sorry to come back. There's now another problem. After I'm exporting the report in HTML format and run the file, got the following error:

"Data provider failed while executing a provider command"
and
"Undefined function 'NZ' in expression".

How can I make it working also in HTML format?

I'm reposting the query:

 
SELECT TotalSumForExpenses1, TotalSumForExpenses2, TotalSumForExpenses3, VAL(TotalSumForExpenses1)+VAL(TotalSumForExpenses2)+VAL(TotalSumForExpenses3) AS TotalSumExpenses, StartingDate, EndingDate
 
FROM
(
SELECT TotalSumForExpenses1
FROM 
(
    SELECT NZ(Sum(IntermediateSums1),0)  AS TotalSumForExpenses1
    FROM
    (
        SELECT TableExpenses1.Price AS IntermediateSums1
            FROM TableExpenses1
            WHERE 
            (
                (
                    (TableExpenses1.Price)=
                    IIf(
                        IsNull(TableExpenses1.Price),
                        0,    
                        TableExpenses1.Price
                    )
                )
                AND
                (TableExpenses1.MyDate>=StartingDate) 
                AND
                (TableExpenses1.MyDate<=EndingDate)
            )
    )
)
),
 
 
(
SELECT TotalSumForExpenses2
FROM 
(
    SELECT NZ(Sum(IntermediateSums2),0)  AS TotalSumForExpenses2
    FROM
    (
        SELECT TableExpenses2.Price AS IntermediateSums2
            FROM TableExpenses2
            WHERE 
            (
                (
                    (TableExpenses2.Price)=
                    IIf(
                        IsNull(TableExpenses2.Price),
                        0,    
                        TableExpenses2.Price
                    )
                )
                AND
                (TableExpenses2.MyDate>=StartingDate) 
                AND
                (TableExpenses2.MyDate<=EndingDate)
            )
    )
)
),
 
 
(
SELECT TotalSumForExpenses3
FROM 
(
    SELECT NZ(Sum(IntermediateSums3),0)  AS TotalSumForExpenses3
    FROM
    (
        SELECT TableExpenses3.Price AS IntermediateSums3
            FROM TableExpenses3
            WHERE 
            (
                (
                    (TableExpenses3.Price)=
                    IIf(
                        IsNull(TableExpenses3.Price),
                        0,    
                        TableExpenses3.Price
                    )
                )
                AND
                (TableExpenses3.MyDate>=StartingDate) 
                AND
                (TableExpenses3.MyDate<=EndingDate)
            )
    )
)
)
;
 

Any ideas?

Thank you,
Cristian

[Scott Price]

Boys, Cristian, Sorry to say I'm a big zero on HTML! Please repost this last section under a new thread with title: "Access Nz() function exporting to HTML?" or something similar to it. Some of our more HTML savvy members can take it from there.

If the Nz() still won't work, you could try the union query idea I suggested in a previous post, to see if it will allow you to accomplish the objective?

Regards,
Scott

[Scott Price]

Another thought here: just reading up on Nz(), it is a Jet specific function, not a VBA function, so I'm not sure if it even will allow upsizing to either TSQL or any other programming language (such as HTML).

Anyway, somebody more smarter than I on this site probably knows the answer "al toque" as we say in Spanish.

Regards,
Scott

[cristip3001]

Hi Scott,

I saw the solution you provided in the previous messages and looks much better than my huge query. I short question: how do you return 0 instead of NULL?

Regards,

Cris

[Scott Price]

Hi Scott,

I saw the solution you provided in the previous messages and looks much better than my huge query. I short question: how do you return 0 instead of NULL?

Regards,

Cris

After playing around with Sum() and DSum() in queries and subqueries and textboxes until I got tired of looking at wrong numbers, I came up with this solution. It's not maybe quite what you want, as it involves the use of VBA code, but it works! (I always like things that work, rather than things that SHOULD work... Smile)

If you're interested in seeing it, let me know and I'll email you the test db I used to construct the thing.

Otherwise, I'm getting short on answers... There are all sorts of hidden pitfalls when you have null values, and the supposedly easy answers haven't turned out to be very easy!

Probably someone else would handle this differently and more easily, but basically I used 6 queries in the end, tied to 6 hidden forms. Then in the report OnOpen event I wrote the code to populate the Expense1Totals, Expense2Totals, Expense3Totals and GrandTotals with the correct values. There's a lot more to it than just that, but that is a brief resumé of what I did.

Regards,
Scott

[FishVal]

Hi, Cristian.

Nz() is VBA function available in Access only.
Some SQL dialects support CASE WHEN..THEN..ELSE and COALESCE operators.

SELECT CASE WHEN (SUM(DummyTable.Price) IS NULL) THEN 0 ELSE SUM(DummyTable.Price),
FROM DummyTable
WHERE DummyTable.Date>=StartingDate 
AND DummyTable.Date<=EndingDate;
 

SELECT COALESCE (SUM(DummyTable.Price), 0)
FROM DummyTable
WHERE DummyTable.Date>=StartingDate 
AND DummyTable.Date<=EndingDate;
 

[Scott Price]

FishVal,

Thanks for your input... A quick comment on your statement here:

Nz() is VBA function available in Access only.
Some SQL dialects support CASE WHEN..THEN..ELSE and COALESCE operators.

Probably a bit nitpicky, but my desk reference says this about Nz:

The Jet expression service supplies the Nz function; it's not a VBA reserved word, so it doesn't appear in bold type.

So technically speaking, it really isn't a VBA function at all. Again, just a bit nitpicky perhaps, but may mean that Nz() will not work in anything outside of Access, even other members of the Office family that use the common VBA programming language (haven't tried it to find out, and don't think I will, either... this comment is just in case someone tries it thinking it SHOULD work, and it won't)?

Regards,
Scott