Need help with a billing cycle query/filter

[vangogh228]

Hi all.

I have been given the task to write an update query (I think) that involves billing cycles. There are two fields involved in this process: Next Bill Month and Billing Frequency. The Billing Frequency is limited to a list of three items, Monthly, Quarterly, or Annual.

What I need to accomplish is the following: The billing person queries/filters for the current month and then sends the bills based on the resulting selctions. Then, based on the Billing Frequency, the billing clerk would run an update to the Next Bill Month field that would change the entry to the correct next bill month.

I can have her filter for the current month, then filter for the frequency three separate times, updating each time manually, but I certainly would like to automate this process.

Any help would be greatly appreciated. I asked a similar question a few weeks ago regarding actual dates instead of months, but then the idea of using dates instead of months was changed (and the answer involved VB code and I did not know how to implement it). Perhaps using month names will make this easier.... at least I certainly hope so.

Thanks again for any help!!!

[This message has been edited by vangogh228 (edited 05-30-2002).]

 

[RV]

Vangogh,

welcome back with your billings.

>I can have her filter for the current month, then filter for the frequency three separate times, updating each time manually, but I certainly would like to automate this process.
<

This rather confuses me. Are you telling that the billing frequency differs for a certain customer?

Why not turn your billing process around (as it is normally):

1) Adjust a (standard) Billing Frequency to each Customer.

2) Then create the form (I assume you use a form) for the Billing clerk based on the billing conditions for customers and the first period to be billed

3) Let the Billing clerk approve the billing itself giving the clerk the possibility to change the Billing Frequency (if she or he is entitled to according to the job...)

4) You could use for instance a checkbox on your form giving the clerk the possibility to confirm the billing. You could use a button to run an UPDATE query for each Billing in the future period which has been checked through the checkbox. And you even could use an extra buttton with a On Click Event to check the checkbox for all Billings automatically to be done for the current payment period and run an UPDATE query.

Just a basic idea, HTH.
See to it that you store all your Billings done for past Periods in a separate History Table.....

RV

 

[vangogh228]

RV: Hmmm... yes, I see I need to clarify.

Customers are on billing cycles of their choice, so each month she will encounter customers who are billed monthly, some quarterly and some annually. If, for example, she is billing for May, she would just filter by selection for that month in the "next bill month" field and then send the bills based on that filter. Then, she needs to update the "next bill month" field to June (for monthly customers), August (for quarterly customers) and stay May (for annual customers).

I know I can query on query results, but I don't know how to UPDATE query on a select query, and don't know how to automate the process for the monthly and quarterly customers each month... unless she goes through a manual process of each query and update step.

Thanks again. Tom

 

[RV]

Tom,

>If, for example, she is billing for May, she would just filter by selection for that month in the "next bill month" field and then send the bills based on that filter. Then, she needs to update the "next bill month" field to June (for monthly customers), August (for quarterly customers) and stay May (for annual customers).<

Use a table which stores all Billings.
Base the form used for the Billing Process on the query.
This query would retrieve all Billings which are due in the month selected by the clerk and have not beej billed yet.
Create a button on the form to confirm the posting of the billings.
Under this button, define an UPDATE query which updates the Billings table (Billed = True) for all Customers who are definitely bileed in the selected month.
Use a checkbox in your Billings table (Billed ) to store the confirmed Billings.

Under the same button (or a button to, for example, close the form), define an INSERT statement which inserts a new Billing row for each Customer based on the Billing frequency.

I'm in a kind of a hutrry right, so I hope this makes any sense.
Perhaps you can send me your database to have a look at it (Access97, zip-file).
Again, store all Billings made!!!!!!!!!!!!!

RV

 

[RV]

Tom,

I've created a little example database.
If you're interested, I can mail it to you.
One thing though, it's developed using a Dutch version of Access...
I could otherwise send you a description, then you'll have to wait a few days...

RV

 

[vangogh228]

RV:

Thanks. I'm a little worried about the version issue. I am still working this out. Any other descriptions are greatly appreciated.

Tom

 

[RV]

Tom,

I'll try to create a short description in a few days.
Can I use your email address in this forum?

RV

 

[RV]

Tom,

I send you a sample database a few days ago.
Did it help you?

RV

 

[dsthomson]

RV Could you share the sample database on the forum or send it to me?
Thanks

 

[dsthomson]

This problem is solved in another thread
http://www.access-programmers.co.uk/forums/showthread.php?t=258485&highlight=billing