Financial transactions

[MMach via AccessMonster.com]

I need assistance with setting up a database structure. I have two tables
(Payments) & (Invoices) with a one-to-many relationship (i.e. one payment can
be assigned to one or more invoices. The issue comes when one payment closes
out one invoice but partially pays the second invoice. (i.e. Payment#1=$3000,
Invoice#1=$2000 and Invoice#2=$2000). The second payment (Payment#2=$2000
would cover the rest of Invoice#2 and part of Invoice#3, etc...). What is
the best method of linking which payments belong to which invoices?

 

[Guest]

A simple one to many relationship doesn't work in this case as One payment
can be applied to multiple invoices, and conversely, one invoice can be paid
by multiple payments. So, just having the paymentid in the invoice table
doesn't capture all the info you would need. Thus, create a Payment details
table that allows this:

PaymentDetails

 

[MMach via AccessMonster.com]

Why couldn't I see this? Your response is spot-on!
Thanks.

A simple one to many relationship doesn't work in this case as One payment
can be applied to multiple invoices, and conversely, one invoice can be paid
by multiple payments. So, just having the paymentid in the invoice table
doesn't capture all the info you would need. Thus, create a Payment details
table that allows this:

PaymentDetails
-----------------
PaymentDetailID
PaymentID
InvoiceID
AmountApplied

I need assistance with setting up a database structure. I have two tables
(Payments) & (Invoices) with a one-to-many relationship (i.e. one payment can

[quoted text clipped - 3 lines]

would cover the rest of Invoice#2 and part of Invoice#3, etc...). What is
the best method of linking which payments belong to which invoices?

 

[Steve]

You have a one-to-many relationship between payments and invoices. In an
abbreviated form you need:
TblPayment
PaymentID
CustomerID
PaymentDate


TblPaymentDetail
PaymentDetailID
PaymentID
InvoiceID
AmountPaidOnInvoice

You don't need PaymentAmount in TblPayment because you can get the total
payment by summing AmountPaidOnInvoice for a specific PaymentID.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[StopThisAdvertising]

You have a one-to-many relationship between payments and invoices. In an
abbreviated form you need:
TblPayment
PaymentID
CustomerID
PaymentDate


TblPaymentDetail
PaymentDetailID
PaymentID
InvoiceID
AmountPaidOnInvoice

You don't need PaymentAmount in TblPayment because you can get the total
payment by summing AmountPaidOnInvoice for a specific PaymentID.

PC Datasheet

--
Hey Steve!! We don't need you here !!
Hey Steve!! We don't want you here !!
Hey Steve!! No-one supports you here !!

==>Let's ask it the 'nice' way...
Would you please, please go away Steve ??

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html (updated, mainly the 'abuse-reporting' page...)
Until now 3850+ pageloads, 2425+ first-time visitors
(these figures are real and rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

[Jamie Collins]

A simple one to many relationship doesn't work in this case as One payment
can be applied to multiple invoices, and conversely, one invoice can be paid
by multiple payments. So, just having the paymentid in the invoice table
doesn't capture all the info you would need. Thus, create a Payment details
table that allows this:

PaymentDetails
-----------------
PaymentDetailID
PaymentID
InvoiceID
AmountApplied

Presumably there is a candidate key (PaymentID, InvoiceID). Any reason
for omitting this from your proposal?

Also, the OP will need a 'law of nature' validation rule to ensure
that for each InvoiceID the sum of the AmountApplied values do not
exceed the amount in the Invoices table (I'm pretty sure that, if
consulted, the auditor would insist on this) e.g.

ALTER TABLE PaymentDetails ADD
CONSTRAINT amount_applied_total_greater_than_invoice_amount
CHECK (NOT EXISTS
(
SELECT I1.InvoiceID, I1.InvoiceAmount
FROM PaymentDetails AS P1
INNER JOIN Invoices AS I1
ON P1.InvoiceID = I1.InvoiceID
GROUP BY I1.InvoiceID, I1.InvoiceAmount
HAVING SUM(P1.AmountApplied) > I1.InvoiceAmount
));

Jamie.

--