Invoicing and Payment Tracking

[reltub]

Hello everyone,

New member here, also very new to access and relational databases in general. I'm trying to design a billing and invoicing database and can't seem to figure out how to link the tables and track the payments made by customers. How can I proceed to track the payments and make payments applicable to certain line items or invoices? If the line item hasn't been paid, it needs to show up on the next invoice and be included in the total due.

 

[AccessMSSQL]

How far have you gotten on this project? Post your table structures so we can see what you have so far. There are probably numerous ways to produce the result you are looking for - it just depends on how you have it set up.

 

[reltub]

Honestly I haven't gotten very far on the project. I think I have my table structure set up properly, but can't figure out where to go from here. Which tables need to be related? I know that the customers table and invoices table needs to be linked. I'm finding it hard to visualize what will happen when I invoice the customer, then the customer makes payment, which will usually happen quarterly. What makes things more complicated is that there is no set fee structure; each client has a different fee.

Since I don't have 10 posts, I can't post an image, but here is the address:

i.imgur.com/NFtiR.png

Thanks for your reply!

 

[reltub]

Here is the basic end result I'd like to see. If an item hasn't been paid, it needs to stay on the new invoice (like the 2nd item Model Validation fees). If it has been paid, it needs to be removed and the client account credited. Do I need a table for each account and the amounts charged (debited) and amounts paid by the client (credited)? That seems kind of ridiculous and I feel like Access can provide a better way of doing this. It's easy to see what all needs to be done, just very hard for me to understand how to get there.

Here is the screenshot of the invoice:

i.imgur.com/qYesd.png

 

[AccessMSSQL]

What I don't see in your relationships is where you actually have the invoice amounts? Each invoice would have many detail items, correct? or are you just going to have an invoice number and amount due? I'll post a screenshot shortly of what I think you should have for your invoicing module table structures.

 

[AccessMSSQL]

Attached is a basic relationship diagram of the tables I think you need. All of your customer rates can be stored in tblCustomerFees table. In your invoicing form you can have the invoice detail amount populate the fee based on the customerID that gets chosen on the invoice.

 

[reltub]

Thanks! I have all the tables set up. Now I'm just confused as to how to populate fields on a report (invoice). I've attached my fees table. Do I need some sort of query to total all these fees for a certain customer?

 

[AccessMSSQL]

I'm not sure I understand how you are using your Fees table? This should be just a reference table? All the fees for each customer stay the same in this table - is that how you are using it? Maybe you can explain your business so I can understand how you invoice customers? Do you bill hours for services and have an hourly rate on your invoice...are you selling a product? Or do customers get billed the same amount monthly/quarterly, etc.?

 

[reltub]

Yes the customer gets charged a fee quarterly for the services in the fields at the top. We charge customers quarterly for Model Validation services, a retainer fee, Management reports, cashflow reports, a one-time setup fee, and a variable fee based on the number of times we have to rerun a report. The base rate for that is $500. So if they have 3 reruns, it would be $1500 total. That's why I am getting so confused - there's no set fee schedule and there are fixed fees, one-time fees, and variable fees involved as well.

The business is providing asset/liability management services for banks (customer). We charge the customer quarterly based on what services we provide for them. The fees aren't set in stone, and are sometimes negotiable or discounted. Sometimes the bank's broker will pay all or part of their fees for them, which is another beast entirely that I will save for another time.

I don't want the one-time fees to show up on every invoice, just the first one for a new customer. I also need the # of reruns to be reset to 0 after quarter end so that the customer isn't charged for reruns in the previous quarter if they haven't had any reruns in the current quarter.

Sorry that this is so long, but the more I think about how to set this up, the more frustrated and confused I get. Thanks for all your help so far.

 

[Joe8915]

just my 2 cents, I have a complete database in just doing this. I have over 10 years invested in it. So if you need any aditional help please let me know. This is the place where I learn it all.

Great group of people here.

 

[reltub]

Hi Joe,

Do you mind giving a quick explanation of how you set up your tables, and the queries you used to maintain your client accounts? I think my table structure is ok, but I have no idea how to manipulate the data so that it will appear on forms and reports. Maybe I can upload a sample db a little later and have you guys point out where I'm going wrong. Thanks again.

 

[AccessMSSQL]

reltub, can you upload a sample? You are going to need to use some VBA programming in the background and you will need to create a main form and a subform for your invoicing and probably a main form and subform for your customers where you can see the customer in the main form and see all of their invoices in the subform.

This article may help you get started:

http://office.microsoft.com/en-us/a...a-subform-a-one-to-many-form-HA010098674.aspx

 

[AccessMSSQL]

also, here are some screenshots of what you could do in your database. Customer form with list of invoices, invoice form, invoice report. This is from a database I wrote for a client.

 

[reltub]

I was afraid I would need some level of programming to accomplish this. Thanks for sharing those screenshots; that looks a lot like what I'm trying to do, except my fee schedule is kind of crazy. I will try to upload a sample today. With all these relationships set up, how can I populate the fields in tblInvoice and tblInvoiceDetail? Do I need a query to 'generate' each invoice?

 

[AccessMSSQL]

My suggestion for you regarding your fees is to not have a fees table with multiple columns representing all of the different fees. You should have a fees type lookup table with these fields: FeeTypeID, FeeName, FeeType. In your invoice details table you also have a feeTypeID field. Join that to the FeeTypeID (autonumber) in your FeeTypes table. So you will have a table that looks like this:
FeeTypeID FeeName FeeType
1 Retainer Quarterly
2 ManagementReport Quarterly
3 Reruns SpecialFees
4 etc...

When you create your invoice, the invoicedetails table is where you will store all of the actual charges incurred by a customer. On your subform that you create, you provide a combo box that links to the feetypes table and as you enter charges in the invoicing form you just go down each row, select the feetype from the drop down list, enter the amount and go to the next line. Choose the next fee type, enter the amount, go to the next line.

Other reason you should have it set up this way is if you ever need to add additional fee types to your invoicing, you just add a new record to this table. You don't have to add a new field or new table.

This way, you also don't have to separate your fees into two tables.

Here's a link to youtube on how to create a combo box.

http://www.youtube.com/watch?v=IbE0bQr563U&noredirect=1

 

[AccessMSSQL]

what version of access are you using?

 

[AccessMSSQL]

example database is attached. Very rudimentary but there is an invoice form with a subform you can use as a simple example. This is Access 2010.

 

[reltub]

Ah ok! I think I'm finally starting to understand whats going on here. Thank you so much for that example!

Let's say I have 40 customers. 35 will be billed quarterly and 5 will be billed annually for the model validation services. Is there a way to create invoices for all 35 quarterly customers at the same time, instead of going through and selecting them one by one? That way, I could build a report with a basic design, then add a 'group by BankName' section, allowing all 35 invoices to be printed out at one time?

Thanks again for all your help

 

[AccessMSSQL]

Yes, you would have to add a new field (BillingPeriod) to your customer table to indicate whether they were billed quarterly or annually. Then, you would also need to indicate which feetypes each customer should get billed for. You would need another table: CustomerFees: CustomerFeeID (autonumber), CustoemrID, FeeTypeID, FeeAmount. You would have to populate this table for each customer. When you go to generate invoices, the system would look at the billingperiod field in customers to choose the customers to bill - then for each customer it would look into the CustomerFees table and grab all of the fees for that customer and insert them into a new invoice detail record. It's possible you could create this functionality all with queries and macros. VBA would be ideal, however.

 

[reltub]

Will I run into problems if a customer needs more than one service, such as Mgmt Reports and Reruns Fees? I feel like I will because I can't duplicate values in tblCustomerFees if it is linked with tblCustomers. Also, if I have this table, doesn't it make tblFeeTypes useless?