Invoice Amt. - Payments = Balance

[prodeji]

Hi all

Here's my situation:

I'm building an invoice database and am designing a form that allows
users to apply payments against invoices.

Because any one invoice may contain multiple items, I had to (?) insert
a subform on the invoice form to record each item's price and quantity
purchased; then subtotaled on the subform footer (I have had hints
after the fact that the subtotal would have better been placed on the
main invoice form; is this true, and why?); to give the invoice amount.

These are the tables I'm using, with (what I think are) the relevant
fields only:

TBLINVOICE
invoiceID (PK)
invoicedate

TBLITEMS
itemID (PK)
cost
quantity
invoicedate (would better be named 'itemdate?')
invoiceID (FK)
paymentID (FK)

TBLPAYMENTS
paymentID (PK)
payment
date
invoiceID (FK)
itemID (FK)

* TBLINVOICES inner join TBLITEMS and TBLPAYMENTS on invoiceID
* TBLITEMS inner join TBLPAYMENTS on itemID


On the payments form (frmPayments) I have inserted a subform
(sfmInvAmt) that calculates the invoice total from the cost and
quantity fields on the "item" subform (sfmItems) on the main invoice
form (fmInvoice).

I tried to do the same thing to calculate the balance remaining using
this query (qryRemaining):

SELECT qryPayments.payment, qryPayments.invoiceID AS
qryPayments_invoiceID, qryInvAmt2.cost, qryInvAmt2.quantity,
qryInvAmt2.invoiceID AS qryInvAmt2_invoiceID
FROM qryPayments INNER JOIN qryInvAmt2 ON qryPayments.invoiceID =
qryInvAmt2.invoiceID;

Of course, it's returning a record for every instance of an item being
posted; which gives me duplicate values for the payments.

I had a vague idea for resolution that involved creating yet another
subform and getting the value from the "InvAmt" subform. I was able to
do that, but I had to set the rowsource of this new subform to the
"InvAmt" subform, which meant I couldn't include any fields from
"qryRemaining" to calculate the balance.

I racked my brain for a few days, but I now concede; I am officially
stumped.

Help.


prodeji

 

[Allen Browne]

The invoice form with a subform for line items sounds right. If the subform
is in continuous view and shows the total at the bottom, that's perfect.
(The idea of displaying the total on the main form is a workaround for
people who want the subform in Datatsheet view.)

It looks like you have tied payments to invoices such that one invoice can
have many payments. You could therefore add a 2nd subform onto your Invoices
form, for the payments. The total payments received for the invoice would
then show in the Form Footer of this 2nd subform.

To show the balance remaining, add a text box to your main form, and set its
Control Source like this:
=[Sub1].Form![txtTotal] - Nz([Sub2].Form![txtTotal],0)

Replace "Sub1" with the name of your first subform (the invoice line items),
and "Sub2" with the name of your payments subform. In each case, replace
"txtTotal" with the name of the text box that contains the total in the
footer of the subform. The Nz() gives the value zero if there are no
payments yet, so it shows the full invoice amount as owing.

(The structure you have chosen does not cope with someone sending a payment
that covers 2 invoices, or a pre-payment for work that is not yet invoiced.
But you might be happy to just ignore those cases.)

 

[prodeji]

Hey Allen

Your analysis of what I was trying to accomplish was pretty good, and
your suggestion right on the money.

I implemented it and it works perfecto.

Thanks a lot, man, I was about ready to pull my hair out!!

The invoice form with a subform for line items sounds right. If the subform
is in continuous view and shows the total at the bottom, that's perfect.
(The idea of displaying the total on the main form is a workaround for
people who want the subform in Datatsheet view.)

It looks like you have tied payments to invoices such that one invoice can
have many payments. You could therefore add a 2nd subform onto your Invoices
form, for the payments. The total payments received for the invoice would
then show in the Form Footer of this 2nd subform.

To show the balance remaining, add a text box to your main form, and set its
Control Source like this:
=[Sub1].Form![txtTotal] - Nz([Sub2].Form![txtTotal],0)

Replace "Sub1" with the name of your first subform (the invoice line items),
and "Sub2" with the name of your payments subform. In each case, replace
"txtTotal" with the name of the text box that contains the total in the
footer of the subform. The Nz() gives the value zero if there are no
payments yet, so it shows the full invoice amount as owing.

(The structure you have chosen does not cope with someone sending a payment
that covers 2 invoices, or a pre-payment for work that is not yet invoiced.
But you might be happy to just ignore those cases.)

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

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

Hi all

Here's my situation:

I'm building an invoice database and am designing a form that allows
users to apply payments against invoices.

Because any one invoice may contain multiple items, I had to (?) insert
a subform on the invoice form to record each item's price and quantity
purchased; then subtotaled on the subform footer (I have had hints
after the fact that the subtotal would have better been placed on the
main invoice form; is this true, and why?); to give the invoice amount.

These are the tables I'm using, with (what I think are) the relevant
fields only:

TBLINVOICE
invoiceID (PK)
invoicedate

TBLITEMS
itemID (PK)
cost
quantity
invoicedate (would better be named 'itemdate?')
invoiceID (FK)
paymentID (FK)

TBLPAYMENTS
paymentID (PK)
payment
date
invoiceID (FK)
itemID (FK)

* TBLINVOICES inner join TBLITEMS and TBLPAYMENTS on invoiceID
* TBLITEMS inner join TBLPAYMENTS on itemID


On the payments form (frmPayments) I have inserted a subform
(sfmInvAmt) that calculates the invoice total from the cost and
quantity fields on the "item" subform (sfmItems) on the main invoice
form (fmInvoice).

I tried to do the same thing to calculate the balance remaining using
this query (qryRemaining):

SELECT qryPayments.payment, qryPayments.invoiceID AS
qryPayments_invoiceID, qryInvAmt2.cost, qryInvAmt2.quantity,
qryInvAmt2.invoiceID AS qryInvAmt2_invoiceID
FROM qryPayments INNER JOIN qryInvAmt2 ON qryPayments.invoiceID =
qryInvAmt2.invoiceID;

Of course, it's returning a record for every instance of an item being
posted; which gives me duplicate values for the payments.

I had a vague idea for resolution that involved creating yet another
subform and getting the value from the "InvAmt" subform. I was able to
do that, but I had to set the rowsource of this new subform to the
"InvAmt" subform, which meant I couldn't include any fields from
"qryRemaining" to calculate the balance.

I racked my brain for a few days, but I now concede; I am officially
stumped.