[AccessD] RE: Database for Small Business

Gary Kjos garykjos at hotmail.com
Tue Aug 24 16:37:33 CDT 2004


OK. I'll bite. You can seed your starting numbers for autonumber fields. The 
most recommended way is to populate the table with one record via a append 
query that sets the value of the autonumber table with one less than you 
want for your first number. then you delete that record and the next record 
added to teh table will have the number you want.....

BUT I think most developers here on list will agree that if you are 
particular about the numbers assigned, autonumbers are NOT the way to go. 
You need to create some code to determine what the last number on your 
system is at the time the new record is being formatted and then use that 
value for your field. Why? Autonumbers will often leave GAPS - if you start 
to add a record but then abort the process for whatever reason, the 
autonumber will still increment and a number will essentially be wasted. For 
non-visible key values this is no big deal at all. But for an invoice 
number, it may be a problem for you and your application as it will need to 
be explained to the accountants and auditors. Generally all an autonumber is 
supposed to get you is a UNIQUE value. yes, they are normally in a sequence 
but that is not guaranteed. If you want a sequential number you need to take 
control of the assignment of those numbers.

Having the KEY to the invoice record be an autonumber is a good thing but 
you might be better off calculating an invoice number that will be displayed 
for the user yourself.

Regarding your question about the additional charges for past due.....Why 
not add another invoice record? You could have a field that associates an 
invoice record with another invoice and then group and total by that at 
statement time?  Or perhaps you need a different table for past due charges? 
It all depends on what you are planning to do with the data. Will there be 
additional charges if the invoice isn't paid for a second cycle????? Or is 
it a one time upcharge? If it's a one time deal you could just have an 
additional field on your invoice table for that charge.

It's really up to you.

Gary Kjos
garykjos at hotmail.com





>From: "Christian, Lorraine" <LChristian at massmutual.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: [AccessD] RE: Database for Small Business
>Date: Tue, 24 Aug 2004 14:53:43 -0400
>
>Yes I definitely understand.  I forgot that our email system
>changed.  I was able to send one unsecure, I hope everyone can
>see that one.  I hope I get some help :)
>
>
>
>-----Original Message-----
>From:
>ime_mailer.4-868094-474642-T4LIIXHL at isimevip01.private.massmutual.com
>[mailto:ime_mailer.4-868094-474642-T4LIIXHL at isimevip01.private.massmutua
>l.com]On Behalf Of accessd at databaseadvisors.com
>Sent: Tuesday, August 24, 2004 2:29 PM
>To: Christian, Lorraine
>Subject: Re: Database for Small Business
>
>
>  Lorraine, you will probably get a better response if you "DONT" post a
>secure message that requires users to log into a 3rd party website.  w/
>so many phishing scams out there, I'm sure you can understand this
>position.
>
>"lchristian" <lchristian at massmutual.com> wrote:
> > Message Redirected by Tumbleweed MMS.
> >
> > Original Recipients:
> > To: "Accessd at Databaseadvisors. Com (E-mail)"
>accessd at databaseadvisors.com
> > Cc:
> >
> > 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
> >





More information about the AccessD mailing list