[AccessD] Database for Small Business

Welcome to the List Lorraine.

Oh, the days when a database could remain simple! <grin>

Okay, to your first question, no, you cannot modify the actual Autonumber
field to display '0001'.  However, you can take 1, and have it displayed as
'0001'.  If if the Autonumber field is CustomerID then in the report,
display it in a text box with this:

=String("0",4-Len("" & [CustomerID])) & [CustomerID]

As for setting an Autonumber to 2004, you can do this.  There are several
methods, probably the easiest is an Append Query:

INSERT INTO tblInvoices ([InvoiceID]) VALUES(2003)

run that, and you'll have a new record with the ID at 2003.  Delete that
record (but do not compact the database), and the next Invoice you put in
will start at 2004. (Once you have added a record, you can compact the
database, and the Autonumber will continue using the next larger number
after the current one.)

As for the late fee, that would be a calculation, no real reason to store
the value in your tables.  Instead, have a Payments Received table, and then
build a query that lists Invoices Not listed in the Payments received table,
along with using the current date compared against a third 'Late Charges'
table, to calculate how much the penalties have added up.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christian,
Lorraine
Sent: Tuesday, August 24, 2004 9:10 AM
To: Accessd at Databaseadvisors. Com (E-mail)
Subject: [AccessD] Database for Small Business


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.

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.  

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.  

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?  

If anyone would like to share with me any tips and/or a sample database I
would
really appreciate it.

Thanks so much,

Lorraine  

---------------------------------------------------------
This e-mail transmission may contain information that is proprietary,
privileged and/or confidential and is intended exclusively for the person(s)
to whom it is addressed. Any use, copying, retention or disclosure by any
person other than the intended recipient or the intended recipient's
designees is strictly prohibited. If you are not the intended recipient or
their designee, please notify the sender immediately by return e-mail and
delete all copies. 

---------------------------------------------------------
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com