Monthly post/batch file - auto change amount

[Ken Ivins]

I have two problems. I am creating a billing database which will bill each
month all active accounts.

First I want to capture that billing so it can be looked at in the future.
Most people call it batching out the month or posting the billing. What is
the best way to do this? Do I create a table on the fly with a name they
give it (default of the date) with just the billing data or is there a
better way?

Second, we will prorate the first month bill. Then we will have a set
billing. I want to have everyone's rate automatically update to the full
month rate when it comes around instead of manually finding all new accounts
and updating the rates manually. I plan to have one table of the clients
with a field for their rate code. Then another table with the various
monthly rate plans (there will be 12 plans) and their codes. This is so that
rates can be changed across the board as needed.

Thanks for your help.
Ken

 

[John Vinson]

I have two problems. I am creating a billing database which will bill each
month all active accounts.

First I want to capture that billing so it can be looked at in the future.
Most people call it batching out the month or posting the billing. What is
the best way to do this? Do I create a table on the fly with a name they
give it (default of the date) with just the billing data or is there a
better way?

There is a MUCH MUCH better way. Storing data, such as a date, in a
table name is extremely bad design.

Rather than "batching out the month", you can simply store each bill
as it is generated, with the bill date as a field. You can use a Query
to *select* all of the bills for any month, current or historic.
You'll have a tall table, with one record for every bill - each
account will have (presumably) twelve records per year.

Second, we will prorate the first month bill. Then we will have a set
billing. I want to have everyone's rate automatically update to the full
month rate when it comes around instead of manually finding all new accounts
and updating the rates manually. I plan to have one table of the clients
with a field for their rate code. Then another table with the various
monthly rate plans (there will be 12 plans) and their codes. This is so that
rates can be changed across the board as needed.

You can use DLookUp to see if there is a prior bill for this customer,
but without knowing more about your table structure it's hard to say
just how or where you would have this code.

 

[Ken Ivins]

John,

Thanks. I wondered about one table. I assume I would "batch" by creating an
append query to this table each month. I'll see how that works. I may have
more questions.

As far as to prorated bill, a DLookup is a good idea. Since this is just
being developed I'm still working on the table design and nothing is set in
stone yet.

Thanks again,
Ken