query question

[j.j.]

Hi, everyone

I have two tables

One is a invoice table including date, invoiceno, amount and then other is
payment table including paiddate,pamount,remarks.

I am using a query from invoice table match payment table. How to find out
which invoice was paid over one time. In fact, I know that some invoice were
settled by the customer in twice.

Any help are appreciated.

Thanks
Jimmy

 

[Allen Browne]

Is there any direct relationship between an invoice charge and a payment
received?

Consider cases such as these:
a) a payment that partially pays an invoice (one invoice paid in many
payments)
b) a payment that covers multiple invoices (one payment to many invoices)

So far, that's looking like a many-to-many relation, so you may be thinking
of creating a junction table between the two? But then there's:
c) invoices never paid (e.g. faulty goods returned, reversals, bad debt)
d) prepayments (amount received when there is no invoice yet)

So, if you can have invoices without payments, and payments without
invoices, is there actually any direct relationship between them?

 

[Ken Sheridan]

Jimmy:

If we assume a simple scenario where each payment record refers to one
invoice (or part of one invoice) only, by means of an invoiceno foreign key
column in the Payments table then you can return, for instance, the number
and sum of payment amounts made per invoice with the following, which uses an
outer join to also include invoices for which no payments have yet been
received:

SELECT Invoices.invoiceno, [date], amount,
NZ(COUNT(*),0) AS NumberOfPayments,
NZ(SUM(pamount),0) AS TotalPaid
FROM Invoices LEFT JOIN Payments
ON Invoices.invoiceno = Payments.invoiceno
GROUP BY Invoices.invoiceno, [date], amount;

Tale note of Allen's caveats, however, if the scenario is more complex.

BTW I'd strongly advise that you don't use date as a column name. It’s the
name of a built in function, so should be avoided as an object name. use
something more specific like invoicedate. If it is used be sure to wrap it
in square brackets when referring to it in a query or code.

Ken Sheridan
Stafford, England

 

[Jimmy]

Hi, everyone

I really said to thank for people to reply my question.

I think that my case exactly Allen said, how to do it ?

Any help are appreciated

Thanks
Jimmy

Jimmy:

If we assume a simple scenario where each payment record refers to one
invoice (or part of one invoice) only, by means of an invoiceno foreign
key
column in the Payments table then you can return, for instance, the number
and sum of payment amounts made per invoice with the following, which uses
an
outer join to also include invoices for which no payments have yet been
received:

SELECT Invoices.invoiceno, [date], amount,
NZ(COUNT(*),0) AS NumberOfPayments,
NZ(SUM(pamount),0) AS TotalPaid
FROM Invoices LEFT JOIN Payments
ON Invoices.invoiceno = Payments.invoiceno
GROUP BY Invoices.invoiceno, [date], amount;

Tale note of Allen's caveats, however, if the scenario is more complex.

BTW I'd strongly advise that you don't use date as a column name. It's
the
name of a built in function, so should be avoided as an object name. use
something more specific like invoicedate. If it is used be sure to wrap
it
in square brackets when referring to it in a query or code.

Ken Sheridan
Stafford, England

Hi, everyone

I have two tables

One is a invoice table including date, invoiceno, amount and then other
is
payment table including paiddate,pamount,remarks.

I am using a query from invoice table match payment table. How to find
out
which invoice was paid over one time. In fact, I know that some invoice
were
settled by the customer in twice.

Any help are appreciated.

Thanks
Jimmy

 

[Allen Browne]

If there is no direct relationship, just create 2 tables: invoices (probably
with an InvoiceDetail table for the line items), and payments.

The amount somebody owes you is then the sum of all their invoices less the
sum of all their payments made. That's not a full accounting system (which
you should probably not be trying to design in Access), but it's enough to
print invoices, receipts, and statements.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, everyone

I really said to thank for people to reply my question.

I think that my case exactly Allen said, how to do it ?

Any help are appreciated

Thanks
Jimmy

Jimmy:

If we assume a simple scenario where each payment record refers to one
invoice (or part of one invoice) only, by means of an invoiceno foreign
key
column in the Payments table then you can return, for instance, the
number
and sum of payment amounts made per invoice with the following, which
uses an
outer join to also include invoices for which no payments have yet been
received:

SELECT Invoices.invoiceno, [date], amount,
NZ(COUNT(*),0) AS NumberOfPayments,
NZ(SUM(pamount),0) AS TotalPaid
FROM Invoices LEFT JOIN Payments
ON Invoices.invoiceno = Payments.invoiceno
GROUP BY Invoices.invoiceno, [date], amount;

Tale note of Allen's caveats, however, if the scenario is more complex.

BTW I'd strongly advise that you don't use date as a column name. It's
the
name of a built in function, so should be avoided as an object name. use
something more specific like invoicedate. If it is used be sure to wrap
it
in square brackets when referring to it in a query or code.

Ken Sheridan
Stafford, England

Hi, everyone

I have two tables

One is a invoice table including date, invoiceno, amount and then other
is
payment table including paiddate,pamount,remarks.

I am using a query from invoice table match payment table. How to find
out
which invoice was paid over one time. In fact, I know that some invoice
were
settled by the customer in twice.