Project consist of taking a rough MS Access Database design and converting it in to a menu driven application that could be adapted to a web hosted database application.
Need to be able to keep track of Certified Letters (Schedule, Sent, Received, Schedule follow-up Mailing of Certified Letter) These letter are sent to each creditors for Each Client. The schedule is dictated by when each mail piece is received by the creditor. example- one client has 10 credit cards, Each Account will receive from 1 to 10 letters each
There is specific information necessary for each Account.
an account may be a credit card account, a Mortgage Account, A collection Account, Auto Loan, Personal Loan, Etc…
Each class of account has different data necessary depending on the type of account. For Example if it is a Mortgage, I need to know the origination lender the assigned lender, the servicing lender, the property that is secured, if it is in foreclosure, I need the lenders attorney, Court information, Judge.
Each client will have a unique 3rd party witness and possible a unique Notary.
Each Certified letter has charges to the client and cost. (Postage, service fee, Notary, Certificate of Delivery, possible 2 misc fees) I need to know who signed for the Certified letter and I need to attach the Electronic Certificate of Delivery which is a PDF document.
Each account will report to various credit bureaus. I need to print out schedule for each client the accounts that are being reported to each credit bureau.
I need to produce an invoice to each client for the mailings.
Need to verify the data like Zip codes as being a valid zipcode (I have the Validation File)
Need to keep a list of Valid Certified Mail numbers so It is not necessary to enter each one separately when entering the certified mail schedule.
Would like to print each clients mail merge letters either by group or individually
Currently I have a “Mail Merge Query” for each type of letter, but, it is cumbersome to go through the process to merge one letter for a client. I need to be able to pick from a list of letters on a menu and automatically open the right Mail merge letter for a client. Each subsequent letter make reference to the previous letters Certified mail information. I have the query worked out. May need a little tweeking though.
Each account is either a client account , a Joint account, or a Spouse account.
I have a different billing schedule for each client. Monthly rate, service fees, notary fees, etc..
A property may have more than one lien on it. for example there may be a First Mortgage from Bank of america and a second Mortgage from Wells Fargo.
I need screens to update the certified mail when it is actually put in the mail (Mailed Date), When I get the electronic receipt, I need enter the date of receipt and who signed for it and attach the PDF receipt document.
Any alteration to the existing table structure will need to update the current database upon completion.
I will supply the current table and query structures, Code needs to be documented for minor alterations. This project will have follow projects.
Key Elements:
1: Maintain Master List of Creditors
2: Maintain Master List of Certified Mail Pieces
3: Maintain Master List of Accounts
4: Maintain Master List of Clients
5: Maintain Billing for Monthly maintenance fees and Mailings.
6: Maintain Master Mail Merge Files list.
7: Maintain Master Property Table
8: Maintain Court Records
9: Maintain Unused Certified Mail Numbers
10:Need to add new creditors, accounts, property, etc while entering other data example add a property while entering the Account that has a lien on the property, or add an creditor while entering account information, or adding an originating lender while entering account information.(a creditor can be an originating lender can also be a servicing lender or it can be an investor. not necessary on the same account).
Please review the files attached. I have included current screen shots.
