Create invoices/cheques for batches

[George]

Dear friends – Sorry for the long post,

I am in the process of developing a Medical Fund database. During each
month employees (both employees and their family members) will bring invoices
to be paid. Also, doctors will send invoices for payment. There are two
cases, Case1: the doctor is a member of the Fund, so employees will not pay
him/her, he/she will send us an invoice each month indicating all employees
visits, and Case2: the doctor is not a member of the Fund, so the employee
will pay him/her and then claim the amount from the Fund.

I have a tbl_Invoices where I store this info. (InvoiceID – autonumber the
primary key, date, Doctor, ChequeNo, and type (1=payment should be made to
doctor, 2= payment should be made to member) etc, related to a
tbl_MembersBenefits –(one to many). In this table I select the benefit, e.g.
operation, the member (each family has a unique code) and the amount.

Once a month I will need to pay all unpaid invoices, either to doctors or to
employees. All invoices of the same doctor should be grouped in order to
issue a single cheque to each one of them.

Also all employees invoices should be grouped in order to issue a single
cheque to each family.

Also I need to record this cheque number in the tbl_invoices in order to
know what invoices are representing by a cheque.

Cheques should be serially numbered.

Any ideas?

Thanking you so much, in advance

GeorgeCY

 

[Allen Browne]

Hmm. Lots going on here.

Firstly, it seems that your invoice table will need separate fields for who
provided the service (the doctor) and who the bill should be paid to (the
doctor or service provider.)

I suggest you use a single table to hold doctors and the funds. Include a
field to distinguish whether the record is for a doctor or a fund, and
another field to indicate who to bill for this doctor (assuming that there
is only one payee for any doctor at any time.)

Now when you create an invoice, you can use the AfterUpdate event of the
doctor field to look up that table and assign a value to the payee field
(either the doctor or the fund -- whichever applies for that doctor.)

This achieves the same result as the 'type' field you suggested, but
actually provides more detail (i.e. it specifies which fund, not just that
it is a fund.) Since it records which fund, this gives you correct
historical information in all your invoices if a doctor changes to a
different fund at some time in the future.

You also asked about aggregating the items from several patient visits in
the period into a single invoice for the period. To do this, you will need
some understanding of VBA and some understanding of queries. Essentially you
need to:
a) Generate a unique number for the batch.

b) Identify all the visits up to the end of the period that have not yet
been billed. (Select query.)

c) Create a header record for each combination of client and payee in the
Invoice table (including the batch number)

d) Insert a record into the InvoiceDetail table for each line item (each
visit) under that combination of client+payee.

e) Design your interface so that visits cannot be deleted or edited once
invoiced (to prevent inconsistencies.)

f) Provide a way to delete the most recent batch (to allow for corrections
and re-runs.)

 

[George]

Thanks Allen for the quick reply,

Please see the following example:

By the end of each month (January in my example) I will have several
invoices which should be payed to doctors, ie.

Doctor A - 1/1/2010 - 100 Euros
Doctor A - 15/1/2010 - 55 Euros
Doctor B - 25/1/2010 -120 Euros

also, some employees and their depentants, i.e.
EmployeeA - 1/1/2010 -50 Euros
SpouseOfEmployee A - 1/1/2010 - 80 Euros
EmployeeB - 15/1/2010 - 115 Euros

I need a way to run "somethings", perhaps select queries, append queries,
update queries in order to achieve the following:

Invoice/Cheque No1 - 155 Euros for DoctorA,
Invoice/Cheque No2 - 120 Euros for DoctorB,
Invoice/Cheque No3 - 130 Euros for EmployeeA and
Invoice/Cheque No4 - 115 Euros for EmployeeB,

Also the next month I will need to select the newer invoices to be paid
(continued from No5 etc)

Any idea?

χÏήστης "Allen Browne" έγγÏαψε:

 

[Allen Browne]

You will need select queries, append queries, and VBA code -- way to much to
address in a newsgroup post.

I gave you the general approach, but we can't give you the many hours of
details it will take to do this.

 

[George]

Thanks again Allen,

I don΄t expect from anyone to spend more than a few minutes for me - I
appreciate very much all efforts and help provided. Just a small question if
is OK,

I managed to find unpaid invoices, then grouped and sum the amount by Doctor
and then append those records to tbl_Cheques. I also, found the way to
record (incrementally) in that table the ChequeNo.

My problem is that I need to record also the chequeNo to each record in the
Invoice table, e.g. chequeNo1 was issued for invoiceNo10 and invoiceNo15 and
chequeNo2 was issued for invoicesNo11 - invoiceNo14.


Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[George]

Found it )) - Thanks a million for your perfect ideas - they make me think
better.

Ο χÏήστης "George" έγγÏαψε: