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 > >