Sum Field

[rbiggs]

I have two tables (Invoice and AddCharge) they are joined on the invoice number. My problem is I am trying to create a query that will have a Total Charge per invoice. Some just have a fee on the invoice while others have a fee on the invoice and one in the Addcharge table. When I try and add the amounts together I get an error since not every invoice # is in the AddCharge table. How can I skip the Addcharge table if the given Invoice does not exist?

Thanks

[alansidman]

You may have to change your join to a LEFT from an INNER. To be sure, suggest you post your SQL statement for your query.

[hertfordkc]

[QUOTE=rbiggs;86593]I have two tables (Invoice and AddCharge) they are joined on the invoice number. My problem is I am trying to create a query that will have a Total Charge per invoice. Some just have a fee on the invoice while others have a fee on the invoice and one in the Addcharge table. When I try and add the amounts together I get an error


If you have a query on the AddCharge table, you can create a calculated variable
Var:Nz(AddChargeFee,0) and then use that to calculate the sum.
If you are doing something with VBA, you need to use nz(var,0) to prevent trying to add a null value into the total.
If you are doing something else, I'd like to hear about it.

[rbiggs]

I used a combination of both. Thank you very much for your help.