Need Help- Adding new data- flow

[dakcg]

Hi,

This is the second time I have come against this problem. I know how to make it work, but there has to be a more logical way:

Simple Invoice database. Three table: Customer, Invoices, Payments. To create the Invoice report I: sum(Invoices) - sum(Payments) to find money owed. I do the opposite to find any credit that might exist. Works great!

Except when you enter a new customer. Since no payments exist for that Customer, when the quieres run they come back with: ERROR.

I have worked around this in an inventory DB by appending a record into the table with a 0 amount. And that works, but I don't think that having incorrect entries into a table is a good thing, especially with count functions.

As anyone come across this problem, and if so, how did you address it?

This is a DB that is in the final testing stages and I Really! want to finish it already!!

Thanks!!!
dakcg

 

[KenHigg]

Not having seen the entire picture, I would speculate that somewhere you need to check to see if the terms used in your calculations are null.

In the expression sum(Invoices) - sum(Payments), can you put something like:

(iif(isnull(Invoices),0, sum(Invoices))-(iif(isnull(Payments),0, sum(Payments)

Basically, if null is detected, use 0, else use the value...

Let me know if need further help, I'm pretty sure this is the problem...

 

[KenHigg]

Forgot about Nz() - Made just for this problem!

 

[dakcg]

?

Would the Null work if the Customer name is in the Invoice table but not in the payment table? I am not sure I understand.

dakcg

 

[Rich]

Your query isn't structured correctly. You should only be looking for customers that exist in the Invoice table. I assume the customer PK is stored as the FK in your invoice table.

 

[Mile-O]

I assume the customer PK is stored as the FK in your invoice table.

The way I'm reading this, I don't think it is.

 

[dakcg]

Problem Solved! Thank you very much!

Thanks to you guys, I have fixed the problem using the isnull. I also changed the join properties to include all from the tblInvoice, which got me the new customer then used the isnull to update the null field to 0.

Thanks again!

dakcg