Link to home
Create AccountLog in
Avatar of Anthony
AnthonyFlag for United States of America

asked on

How to handle retro termination/billing in Access database program?

HI All,

This is more of a brainstorming question, then an actual technical question.  Currently my organization has a program that was created on an IBM iSeries for dues billing.  The billing is based on the number of weeks that a person works in a month.  Parts of this program aren't working correctly so I've been asked to re-write it in an Access database format, which is great.  I've completed the first phase of the project which is to determine how much I need to bill for each week and then I sum that by month.  I have two outstanding issues that I don't know how to handle...

1. I don't know how to handle a retro billing instance.  For example, if we receive a date hire 2 months later and dues should have come out for one month already, how do I create a separate procedure for that billing?

2.  I also don't know how to handle retro termination arrears removal.  For example, if I billed out for the month of March, but they only worked for 3 weeks in March, how can I capture that change correctly.

I'm really just looking for some ideas on how to tackle this, not the in-depth technical part on creating it...

Avatar of PatHartman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Anthony


Pat, thanks for the great explanation.  That is what I was thinking of doing.  Because we bill in a slightly unique calendar, I thought it would be better to create my own calendar.  The calendar is broken out by these columns:  Year_Week_Number, Bill_Month, Week_Number_Within_Month, and each Saturday as a date in the Month.  I can then work with that to determine both the retro billing and the removal of arrears.  But thanks for solidifying that multiple processes are the way to go.

Sounds like you can do something similar to my "never bills" process.  When we first started billing, we only checked the last bill date but it turns out that due to the way the client manages the care plans, there could be "adds" that were actually replacements and had actually been billed so we expanded the check to include looking into the billing file to determine if we had already billed for the service.