Forming correct tables

[pirateprof]

I need to set up a database to record invoices (only the # and total amt, no line item breakdown). I then need to apply payments against the invoice(s) and find the current balance. Thus far I have the following:

tblCustomer (CustID, other fields)
tblInvoice (Num, CustID, Date, Amt)
tblPayment (PymtID, InvNum, Date, Ck#, Amt)

Right now an Invoice can have many payments attached to it and a payment can cover many invoices, thus many to many.

What would be the correct intersection table to use, for some reason, it has me stumped. Any help appreciated.

Thanks!

 

[HiTechCoach]

I need to set up a database to record invoices (only the # and total amt, no line item breakdown). I then need to apply payments against the invoice(s) and find the current balance. Thus far I have the following:

tblCustomer (CustID, other fields)
tblInvoice (Num, CustID, Date, Amt)
tblPayment (PymtID, InvNum, Date, Ck#, Amt)

Right now an Invoice can have many payments attached to it and a payment can cover many invoices, thus many to many.

What would be the correct intersection table to use, for some reason, it has me stumped. Any help appreciated.

Thanks!

I would use a payment allocation table that is a junction table between the tblPayment and tblInvoice to allocate the payment.

Something like:

tblInvoicePayments
- InvoicePaymentsID - primary key - auto number
- InvoiceID - foreign key to the  table tblInvoice  
- PaymentID - foreign key to the  table tblPayment  
- Amount Allocate4d - currency

 

[pirateprof]

The Inv Amt needs to be offset by the Payment amount so that I can calculate the bal on an invoice. Will this junction table do that? I'm not sure.

 

[HiTechCoach]

The Inv Amt needs to be offset by the Payment amount so that I can calculate the bal on an invoice. Will this junction table do that? I'm not sure.

Yes the junction table will allow you to do that.

You sum the junction table records by invoice to get the total payments for an invoice. This amount can be calculated in a query and subtracted from the invoice total to get a balance due.

 

[pirateprof]

Thanks much, I will work on it today and if any problems will repost. Appreciate your help.

 

[pirateprof]

I have the final as follows: (Note question below)




Data Entity Fields Key Type tblInvoice InvNum P Text
InvCustID
Text
InvDate
D/T
InvAmt
Curr
InvNotes
Memo



tblNonInvoice NonInvID P Auto
NonInvCustID
Text
NonInvItemNum
Text
NonInvCaseAmt
Num
NonInvPieceAmt
Num
NonInvDesc
Text
NonInvNotes
Memo



tblCustomer CustID P Text
CustName
Text
CustAddress
Text
CustCity
Text
CustState
Text
CustZIP
Text
CustPhone
Text
CustDelDay
Text
CustNotes
Memo



tblInvoicePayment IPID P Auto
IPInvNum
Text
IPPymtID
Num
IPAmtAllocated
Curr
IPNotes
Memo



tblPayment PymtID P Auto
PymtInvNum

Text
PymtDate
D/T
PymtCk#
Text
PymtAmt
Curr
PymtNotes
Memo

When I set up a Payment type form, how would I show the customer for who the payment is being made? Should I add the Cust ID to the Payment table (not as a link) but to be able to add a subform to show the customer? Or is there a better way? Thanks

 

[pirateprof]

Forget the above as it did not paste properly. I will post an image below.

 

[pirateprof]

I tried uploading a doc file to show my design along with the question I had above. Thanks.

 

[HiTechCoach]

The payment table should probably look something like this:


tblPayment

PymtID P Auto
PymtCustID Text ( link to customers - customers make payments, not an invoice)
PymtDate D/T
PymtCk# Text
PymtAmt Curr
PymtNotes Memo


The table tblInvoicePayment is use to like the above payment to an invoice.

 

[pirateprof]

Final should look like this, right? Thanks for your help!

Customer
CustID
Other Fields

Invoice
InvID
InvCustID
InvDate
InvAmt

Payment
PymtID
PymtCustID
PymtDate
PymtCk#
PymtAmt

InvoicePayment
IPID
IPInvID
IPPymtID
IPAllocAmt

 

[HiTechCoach]

Looks great to me. Good Job!

 

[pirateprof]

I last question...

Since I could not enter both an Inv ID and a Payment ID (re: recording an invoice or paying an invoice) on a single entry those could not be required fields, am I right? or am I missing something.

Thanks so much for your help.

 

[HiTechCoach]

I last question...

Since I could not enter both an Inv ID and a Payment ID (re: recording an invoice or paying an invoice) on a single entry those could not be required fields, am I right? or am I missing something.

Thanks so much for your help.

I do not think that is correct. The reason you are normalizing your data correctly is to avoid what you are describing.

The table InvoicePayment will only have a record added when a payment is actually recevied and then linked to an invoice(s).

 

[pirateprof]

I need to be able to do the following:
1) Record an invoice (total amount only) linked to the Cust ID.
2) Be able to record a payment against an invoice (both partial payments, and payments for multiple invoices)
3) On a form, when I am ready to record a payment I need to be able to selected a customer id to apply the payment to and see all the outstanding invoices against which to apply the payment. I am not sure how the InvoicePayments table is going to work to do that. Suggestions?

 

[HiTechCoach]

1) Record an invoice (total amount only) linked to the Cust ID.

I would create a form and use a combo box to select the customer

2) Be able to record a payment against an invoice (both partial payments, and payments for multiple invoices)

I would create a form and use a combo box to select the customer

3) On a form, when I am ready to record a payment I need to be able to selected a customer id to apply the payment to and see all the outstanding invoices against which to apply the payment. I am not sure how the InvoicePayments table is going to work to do that. Suggestions?

On the same for for recording the payment (see #2), I would add a sub form for the to InvoicePayments table.

You will need to decide how you want to build a GUI to help the user select the available invoices that have not been paid.

I am not sure how the InvoicePayments table is going to work to do that.

The table only will record the selected invoice. A form will need to be used to handle the selction of invoices.

I have done this several times before. It did require lots of VBA code and temp tables to make it very easy fo rhte user.

 

[HiTechCoach]

So are you now continuing this here:

Invoice/Payment tables problem