Set a default value if there are no records

[Lateral]

Hi guys,

I hope I can explain this issue properly.

I have a table called "Payments" and a table called "WorkOrders". The "Payments" table contains a record for each payment that is made against a Workorder ("WorkOrder" table). There may me multiple payments agains a Workorder.

A WorkOrder is the same as an invoice. Not all WorkOrders will have payments against them.

I'm creating a report that uses the following query to total up all of the payments for each Workorder and insert it into the report...I have all of this working well and I even have some other calculations as well.

My problem is that I want to display a column called "Owed" that shows how much money for each Workorder, is owing after taking into consideration all of the payments that have been made against that Workorder. I have this logic working well. The issue is when there aren't any payment records in the "Payments" table. There is no value for me to calculate. Is there a way I can set some sort of default value to be used in the event that no records are found? I would like to set the default to "0"....

Thanks for your help.

Here is the query I am using to sum the records in the "payments" table:

SELECT DISTINCTROW Payments.WorkorderID, Sum(Payments.PaymentAmount) AS [Total Payments]
FROM Payments
GROUP BY Payments.WorkorderID;


Regards
Greg

 

[Minty]

Try replacing Sum(Payments.PaymentAmount) with
NZ(Sum(Payments.PaymentAmount),0)

 

[spikepl]

There is no value for me to calculate.

You need to show what you are doing - the entire context, not just tidbits.

You'd need to do a LEFT JOIN on the Workorders to get the nonexisting records to show. DISTINCTROW is distinctly weird in your SQL. Why are you using it?

 

[Lateral]

Hi Minty,

I just tried your suggestion and it works great! Thanks!

Spikepl, somebody else created this database and I'm just trying to maintain it...

 

[Lateral]

Hi Minty,

I just noticed the format has changed from "Currency" and I can't change it back....any ideas?

 

[Minty]

Hi Minty,

I just noticed the format has changed from "Currency" and I can't change it back....any ideas?

Format(NZ(Sum(Payments.PaymentAmount),0),"Currency")

 

[Lateral]

Thanks Minty,

Where do I put Format(NZ(Sum(Payments.PaymentAmount),0),"Currency ") ?

This is what I currently have:

SELECT DISTINCTROW Payments.WorkorderID, NZ(Sum(Payments.PaymentAmount),0) AS [Total Payments]
FROM Payments
GROUP BY Payments.WorkorderID;

 

[Lateral]

Hi Minty,

I figured it out!

Works great!

Thanks again for your help!

Regards
Greg