ms access 2010 - SQL - Filter a query result with an additional condition -

i have database 2 tables: - credit records payments have receive - payment records payments have received

the structure credit: id, total payment: id, received

my goal have query print payments still not paid. wrote query prints - id of credit - total money request - total money received need add condition: don't print records payment done. sql code:

select credit.id,        credit.total,        (select sum(payment.received)        payment payment.id = credit.id) totalreceived credit credit.total > 0; 

i tried changing last row "where credit.total > totalreceived" doesn't work.

may me please? :)

ps. in credit id unique while in payment table can present many rows same id.

this should credit records have not been paid. payment table should have creditid field join instead of on credit.id = payment.id if that's how it's set should work.

select  c.id,         c.total,         sum(nz(p.received,0)) totalreceived    credit c         left join payment p on c.id = p.id --not sure this?   c.total > 0 group c.id, c.total having  c.total > sum(nz(p.received,0))