Invoice/Payment query

[Guest]

I'm not new to Access, I'm very new!

I have 2 tables: Invoice (InvoiceNo, InvoiceValu, Invoice Date) and
Payment(PaymentID, PaymentDate, PaymentValue, InvoiceNo)
I need a list with not paid invoices (the amount for every invoice). I made
a query with a calculated field : [InvoiceValue]-Sum([PaymentValue)]
and the not paid amounts are well calculated, but only for those Invoices
that I've made some payments.
ex: InvNo: 101 Value 1000 PaymentID:1 PaymentValue:100 Invoice
No:101
102 2000 PaymentID:2 PaymentValue:200
InvoiceNo:101

When I run the query I get only : InvNo:101 Not Paid:700

How do I solve this? i tried something with Nz, IsNull... but got stuck.

Thanks a lot

 

[Rob Parker]

You've probably got an Inner Join (the default in the query-builder) between
your Invoice table and Payments table; what you need is a Left Join. You
can either simply edit this in the SQL view of the query, or change it in
the query builder by right-clicking on the line joining the Invoice number
fields, selecting Join Properties, and clicking the radio button for "2:
Include ALL records for 'Invoice' and only those records from'Payment' where
the joined fields are equal."

Rob

 

[Guest]

That was so easy...feel ashamed now that I even asked. Thanks so much Rob!

You've probably got an Inner Join (the default in the query-builder) between
your Invoice table and Payments table; what you need is a Left Join. You
can either simply edit this in the SQL view of the query, or change it in
the query builder by right-clicking on the line joining the Invoice number
fields, selecting Join Properties, and clicking the radio button for "2:
Include ALL records for 'Invoice' and only those records from'Payment' where
the joined fields are equal."

Rob

I'm not new to Access, I'm very new!

I have 2 tables: Invoice (InvoiceNo, InvoiceValu, Invoice Date) and
Payment(PaymentID, PaymentDate, PaymentValue, InvoiceNo)
I need a list with not paid invoices (the amount for every invoice). I made
a query with a calculated field : [InvoiceValue]-Sum([PaymentValue)]
and the not paid amounts are well calculated, but only for those Invoices
that I've made some payments.
ex: InvNo: 101 Value 1000 PaymentID:1 PaymentValue:100 Invoice
No:101
102 2000 PaymentID:2 PaymentValue:200
InvoiceNo:101

When I run the query I get only : InvNo:101 Not Paid:700

How do I solve this? i tried something with Nz, IsNull... but got stuck.

Thanks a lot