Thread: Need some advice on structure
12-20-04, 19:39 #1Registered User
- Join Date
- Sep 2004
- Raleigh, NC
Unanswered: Need some advice on structure
I have a database that's already built and I need to implement a new "process" into this database. For those of you who are brave and in the giving mood this holiday season, please read along and try to understand my goal. This is the last part of the database I need to finish, so any and all advice on the topic is more than greatly appreciated.
The database tracks classified ads in a newspaper and stores them in a 1-to-Many relationship with customer accounts. (1 customer may have many classifieds tried to their account) I had previously designed the database with a classifieds table (tblClassifieds) that contained the field [Payment] which stored the amount due for a particular classified ad. I have a form that contains text boxs for entering the ad, the dates that it's running, and the rate by which to charge. On the same form, the [Payment] is a calculated value that takes information from each of the previously named objects. The [Payment] is then stored in tblClassifieds for later use and updating the information in the classified form, would ultimately update the [Payment] within the table. I now know that this design was poor and I have learned from the mistake. Unfortunately, all of the data is already in the table, so I must find a way to accomplish the following without restructuring the database. I need a workaround so to speak.
I needed a way to apply interest to these ads in the event that the due date passes and no payment was made. I was able to accomplish this with a nested IIF statement within a query - I then output the results to a subform and SUM the totals for a grand total. I can generate an invoice for a customer no problem now, but the invoices only reflect the amount due, they don't show payment information. Here is where my problem lies.
We now need to start processing payments for these ads, but I'm completely dumbfounded as to how to approach this. I need a way to select 1 or more ads (I was thinking a list box, but couldn't get it to work) and then total these ads into a grand total. We need a way to accept both partial payments and full payments, so I figured I need a method by which to deduct from the total amount due for a classified until it reaches 0. Once the ad reaches 0, obviously I wouldn't want it to appear on the invoices any longer. I'm sure that I can handle this part (removing paid ads from the invoice), but it's the actual payment that I'm unsure of.
I've been racking my brain for 3 days on this now and I really need some help figuring out how to tackle this. If anyone out there has any free time and thinks they might be able to lend me a hand or some advice, please do so. As always, thanks to everyone for their help and happy holidays.
12-21-04, 07:49 #2Village Idiot
- Join Date
- Jul 2003
Well, I would think you would want to keep track of the interest that has been paid, otherwise, you'll be scratching your head in 2 years trying to figure out why customer X paid 20, when his amount due was only 18. Your accountant would probably like to have that detail, also.
I would add "Interest_Due" and "Interest_Paid" columns to the tblClassified (default to 0). So, you should have Invoice Amt, Interest_Due, Intererst_Paid, and Payment fields in the table. You can calculate anything else you need (Amt Due = (Invoice_Amt+Interest_Due)-Payment)
Next, I'd create a payment form that shows all of the invoice detail for each record. Your base recordset for the form would be all records that have an Amt Due. Use a combo box for your invoice number, so you can skip to that record when updating invoices. Put an unbound text box on the form to enter a payment into. In that box's after_update event, create a macro (or code), to: first pay off interest due, add the interest paid to the interest paid field, then apply the balance to the payment field.Inspiration Through Fermentation