Problem showing accounts with no activity on a query

[Guest]

My system deals with accounts of a sort. Basically, there are 3 tables:
accounts, purchases on these accounts, and payments to these accounts.

i want to do a query of the total amount that has been paid to each account.
i take fields from the accounts and payments table. this is, of course done
simply by a sum function on the £ amount field, but accounts that have had no
payments against them do not appear, where i would like them to appear as
£0.00 paid so all accounts are visible.

Is there a solution other than to enter a payment of £0.00 to each account
when it is opened (much prefer not to as end user will be not tech savvy and
time-poor)

please help me someone!!

 

[Brian Bastl]

dbl-click the join line between the tables, and select join properties. then
select the option to show all records from your account table and only those
values in your payments table where the joining fields are equal.

HTH,
Brian

 

[Guest]

ok, all accounts now appear BUT still doesnt work. the count of payments
shows 0 for those accounts that have not had a payment. the sum of amount
paid for these accounts is blank altogether, and neither amount can be used
in another calculation (which is what i now need to do). any ideas? sorry to
bother you with it twice

 

[Brian Bastl]

I'm thinking that your SQL should look something like:

SELECT tblAccounts.AcctID, Sum(nz([Payments],0)) AS [TotalPayments],
Nz(Count([Payments]),0) AS Instances
FROM tblAccounts LEFT JOIN tblPayments ON tblAccounts.AcctID =
tblPayments.AcctID
GROUP BY tblAccounts.AcctID;

You'll have to change the names as necessary.

HTH,
Brian

 

[Guest]

yes!!!

works now, project saved.

cheers

I'm thinking that your SQL should look something like:

SELECT tblAccounts.AcctID, Sum(nz([Payments],0)) AS [TotalPayments],
Nz(Count([Payments]),0) AS Instances
FROM tblAccounts LEFT JOIN tblPayments ON tblAccounts.AcctID =
tblPayments.AcctID
GROUP BY tblAccounts.AcctID;

You'll have to change the names as necessary.

HTH,
Brian

ok, all accounts now appear BUT still doesnt work. the count of payments
shows 0 for those accounts that have not had a payment. the sum of amount
paid for these accounts is blank altogether, and neither amount can be used
in another calculation (which is what i now need to do). any ideas? sorry to
bother you with it twice