Sum in Query to return 0 (1 Viewer)

[shawnacker]

I have looked through past messages and found two possible solutions to my question, but I am not sure which is the best to apply to my problem. Should I use Nz or IIF???

I have a two tables. One table includes many account names and one which includes some of the same account names (never any other names though) and numbers which when added up should give a total to each account name.

Ex. of second table

AccountName Activity
138a 500.50
138a -650.36
138a 100.00
255b 500.35

What I want to do is total up the activity based on the account name and if there is no activity I want the return to show 0. The account names need to come from the first table since it houses all of the account names. So my desired result would look as such:

AccountName Activity
138a 49.86
200a 0
210b 0
255b 500.35

Currently I just have a basic query which sums up the activity by account name and lists its total. I have included the second table to have a basis for the account names that have 0 activity to be added up.

How do I want to build my query function so that I can show all of the account names and show the proper totals, either with activity or not?

Any help would be greatly appreciated.

Thank you,

Shawn

 

[Pat Hartman]

Select A.AccountName, Sum(Nz(B.Activity)) As SumActivity
From tbl1 as A Left Join tbl2 as B on A.AccountName = B.AccountName;

 

[shawnacker]

Pat,
You are the greatest. I just adjusted the join type in my query and it worked perfectly.

Thank you.