[AccessD] RE: Database for Small Business

Read inline...

On Tue, 24 Aug 2004 14:53:43 -0400, Christian, Lorraine
<lchristian at massmutual.com> wrote:
> > Good morning,
> >
> > I would like to develop a very simple database for a small business.
> > To start off I believe I need a customer table and an invoice table.
> >
> > The customer table will have basic customer information and
> > a CustomerID that is an autonumber.
> >
> > The invoice table will have billing information and an InvoiceID
> > that is an autonumber.
> >
> > First question, can you customize autonumbers?  For
> > example I want my first customer to have an ID of 0001
> > and my first invoice to have an ID of 2004.

yes you can customize it to be 0001 as display,  Of the top of my head
you should be able to just insert via an insert query an empty record
where the PID (invoiceid) is 2003. so the next first real record will
be 2004
but for all general purposes, you'll need to remember that Autonumbers
follows the "surrogate key" rule, and therefore is meaningless, and
you will have a difficult time keeping this number in sequence such as
0001,0002,0003 etc., instead it may be more reflective of:
0001,0003,0004,0006, 0007,0009 etc...

that's because order number 0002 could have been canceledlike order
0005 and 0008.

> > Second question, each record in the invoice table will be
> > for every invoice that is sent out.  I am stuck on how to
> > handle late payments.  I would like to charge 1.5% for
> > over 30, 60 and 90 days.

If you add a calculation field into your query you can get this
number, that means that the value of this field will always change
since it is dynamic.  One option is to calculate the date from the
invoice due date w/ the current date. which gives you the total number
of days, then you can multiply your 1.5% times the remaining balance. 
 A DateDiff Function seems the most appropriate for this task. such as

iif(datediff("d","5/1/2004","8/24/2004",vbSunday,vbUseSystem)>30,
<calculation for over 30 days>,  <calculation for under 30 days>)


> > So for example.  I send out a monthly invoice for customer 0001,
> > he/she does not pay on time.  I now need to send out a
> > second invoice not only charging the customer for the current
> > month, but also the payment that is overdue (last month).  And what
> > happens if the customer still does not pay and payments become 60 or
> > 90 days past due?   I am not quite sure how to design this table in
> order to handle
> > what I am trying to accomplish.

Seems to me you'd want to work this out in a query with similar
functions as outlined above.  Additionally, I'm not one to step on
your toes, but this kind of accounting can genarlly be handled by
other programs like Quicken / Quickbooks etc.  Is that not a viable
option?

> > Finally, the actual invoice (report) has separate fields for current,
> 30 days, 60 days, etc.  In
> > addition to that I has a finance charge field.  Originally I was going
> to do a simple
> > calculation on the report for the finance charge.  But after thinking
> about that, do you
> > think I should permanently store the finance charge in the invoice
> table?

The finance charge for each cycle should be stored, but the
calculation on the incurred cost need to be calculated on each run. (i
would think, ymmv)

Good luck.

-- 
-Francisco