Table Design Help / Query

[LTOSH]

Ok i know this is probably more simple than my mind will allow at this point
so i'm asking for help.

I have a client database that I am creating that I want to do a basic
billing/payment component. There are approximately 5 different services each
with their own cost. (For example...One-on-One Training - cost is $35.00 an
hour.) So currently i have a client table established with the following
fields:
cClientID - Autonumber
CFirstName
CLastName
*other basic demographis info*

I want to have a subform (that looks like a datasheet) at the bottom of a
client's form to show what is being charged and a payment and the current
balance.
but i want a button to click to add a new service and also a button to add a
payment.

i hope this is somewhat clear to what i am desiring...it is basically an
billing/payment that i want to create invoices for and keep up with the
accounting aspect. any help on the design of the tables would be appreciated
OR if you know where i can find a good (simple in terminology) template to
look at and learn from. All the templates i have found do not include the
payment tables and examples.
I am using Access 2002.

Thanks!!!

 

[Allen Browne]

Could it ever happen that you want to invoice a client for 2 things at once?
If so, you need tables like this:

FeeType table, with one record for each type of fee, and a FeeTypeID primary
key.

Invoice table, with fields:
- InvoiceID primary key
- ClientID relates to the primary key of your Client table
- InvoiceDate Date/Time
- InvoiceHead Memo comments to show above details
- InvoiceFoot Memo comments to show below details

InvoiceDetail table, with fields:
- InvoiceDetailID AutoNumber primary key
- InvoiceID Number relates to Invoice.InvoiceID
- FeeTypeID relates to FeeType.FeeTypeID
- Fee Currency how much

Now one invoice can contain multiple line items. You nees a main form for
the invoice, and a subform for the line items.

 

[LTOSH]

This helps greatly...Yes it could be possible for an invoice to bill for 2
things at once...but in the billing i would need a field to indicate how many
hours billing for/or months billed for.
for example.
one on one training is $35 hour....client is billed for 5 hours...i want the
total billed on the invoice.

which table do I put this field in.

question 2....what about a payment table....when they send in the payment i
want this to be inputed in the database...can you please give me an idea how
that table would be set up based on the two tables you have described.

question 3....now that i have these tables set up i want to put a "subform"
in the form of a datasheet on the to show me the account status. to show what
is billed, the date billed and the amount billed and the balance owed...then
to also show payment date and payment amount with the updated balance.

 

[Allen Browne]

A 1:
Use fields like this in the Invoice table:
- Quantity Number (Double) how many hours or seminars or whatever.
- FeeEach Currency how much per hour or seminar or ...

Then create a query using this table, and type this into the Field row:
Amount: [Quantity] * [FeeEach]
Use this query as the source for your subform.
Show the subform in Continuous Form view.
In the Form Footer section, include a text box with Control Source:
=Sum([Amount])
That shows the invoice total.

A 2:
Store Payments received in a different table. I suggest you don't make any
direct connection between the 2 tables, because all sorts of weird things
happen in the real world, such as:
- Multiple payments of one invoice;
- Partial payments;
- One payment that covers multiple invoices (or parts of invoice);
- Prepayments (i.e. money received before any invoices is written out.)
So the simplest solution is just to sum all the invoices for a client,
subtract the payments received, and the difference is what is owing.

If you must go beyond that, you may need to understand how double-entry
accounting works.

A 3:
I suggest a different form for payments received. If you wish, you can show
a balance owing for the client on your form (a DSum() expression.)

Once you have the above working, there is another approach that I've found
very useful, and that is to have just one pair of tables to store invoices,
receipts, credits (effectively negative invoice), and refunds (effectively
negative receipts.) The main table has a field that indicates the
transaction type. The TransactionDetail table contains the line items
(Quantity, FeeEach, etc.) The transaction type table has a Multiplier field
that contains 1 (for positive) or -1 (negative.) In a query you can
therefore get the amount as:
Amount: [Multiplier] * [Quantity] * [FeeEach]
Then when you DSum() that amount for any client, you know what they owe.

This alternative is very flexible, but you may find it easier to take
onboard after you have examined the first approach.