Design for a service business

What is the best way to design a database for my service business?

Invoices are sent out just once a month and the customers pretty much stays 
the same from month to month.  

Each customer has a customer number and each invoice has an invoice number 
(the invoice number is really just an abbreviation of the month and year 
attached to the customer number).

Should I have a seperate table for each month or do it some other way?

Presently, using different database software, I create a different database 
file for each month.  

Many thanks.
0
jayC
1/25/2008 4:37:39 PM
access 16762 articles. 3 followers. Follow

2 Replies
603 Views

Similar Articles

[PageSpeed] 20

1st off, and I don't mean to scare you, but developing such a piece of 
software involves a lot of work.  I typically advise my clients to simply 
purchase an accounting package (Quickbooks, Dynacom,....)  cheaper, well 
developped....

That said, no, you do not want seperate table for each month.  Simply have a 
master table that has a date field.  Then you can build your queries to 
filter by day,  month and/or year.  You'll want the ease of use for other 
functionalities such as year end reports...

Also, take a look at

http://office.microsoft.com/en-us/templates/CT101426031033.aspx

There are a few sample databases that you can inspire yourself from.
-- 
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



"jayC" wrote:

> What is the best way to design a database for my service business?
> 
> Invoices are sent out just once a month and the customers pretty much stays 
> the same from month to month.  
> 
> Each customer has a customer number and each invoice has an invoice number 
> (the invoice number is really just an abbreviation of the month and year 
> attached to the customer number).
> 
> Should I have a seperate table for each month or do it some other way?
> 
> Presently, using different database software, I create a different database 
> file for each month.  
> 
> Many thanks.
> 
0
Utf
1/25/2008 5:21:02 PM
On 25 Jan 2008 16:37:39 GMT, jayC <j570c@yahoo.com> wrote:

>What is the best way to design a database for my service business?
>
>Invoices are sent out just once a month and the customers pretty much stays 
>the same from month to month.  
>
>Each customer has a customer number and each invoice has an invoice number 
>(the invoice number is really just an abbreviation of the month and year 
>attached to the customer number).
>
>Should I have a seperate table for each month or do it some other way?
>
>Presently, using different database software, I create a different database 
>file for each month.  
>
>Many thanks.

A table per month!? Yuck. Not in Access; it might (barely) make sense in some
older programs.

No, you have a One (customer) to Many (invoice) relationship. You need two
tables, with a CustomerNumber as the primary key of the customer table, and as
a foreign key in the invoices table. I would discourage the use of your
current composite invoice number; fields should be "atomic", having only one
piece of information. You're storing three (customerID, year and month);
that's hard to maintain and inflexible (suppose you someday need to send a
customer an extra invoice during a month? Redesign your table to accommodate a
suffix to the invoice number!?)

Take a look at these resources, and at the Northwind sample database (which
handles customers, sales and invoices) as an example.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

             John W. Vinson [MVP]
0
John
1/25/2008 6:40:03 PM
Reply:

Similar Artilces:

Business Units 03-08-05
I have an organization with a top level Organization and the 4 subsidiary B U’s. This is all set up in CRM and the CEO can see leads across all BU's. The problem that I am having is that a sales manager can also see all leads through out the organization also ie, leads that they do not own and have not been assigned to them. The sales manager BU is set so that members can only see (read) the leads in their own business unit. Structure – Organization – CEO Business Unit A – sales manager A Business Unit B– sales manager B Business Unit C– sales manager C Business Unit D– sales...

Service planning
Hy, I can make a plan for peaplo who don't have CRM ? I have see the ability to plan ervice based on CRM user but other technical peaple who don't are CRM ? How can we plan their work? Sory for my english and thanks for any idea. Lorenzo Pergine Valsugana - Italy ...

Event Service hangs after SP2 installation
Hello NG! After installing Exchange 2003 SP2 at a SBS 2003 machine the event service hangs during shutdown the machine with the following message: MSExchangeES ID: 7 (0x80004005) unexpected error in maintenance thread I�ve checked MS-Kb but cannot find anything. Can anybody help? Thank you! Holger ...

How to print 4 page design on 4.5" x 5.5" top fold cards
I have designed a 4 sheet (front, middle 1, middle 2, and back) as an invitation. This is set up as a 4.25"x 5.5" top fold card. How do I set up Publisher and HP 7280 All-in-one printer to print on card stock. I expect to use two side printing, but Publisher does not have print set up for these standard size cards. You will need 4.25" x 11" card stock OR use 8.5" x 11" stock which you plan to cut to size after printing the invitations. I'm going to assume you will be using 8.5" x11" stock. This will create two invitations per sheet of...

Logon issues with CRM services
Greetings everyone! Currently all of the CRM services are running under the domain admin account. About once a day, CRM will not open, and I'll have to go into the MMC, re-enter the domain admin's password to give the accout logon rights for the service, and restart the service for CRM to be accessible again. This is a serious pain since it happens about once a day. Does anyone know why this occurs and how I can fix it? Michael Losapio Support/Developer TwinEngines, Inc. ...

Gaps appear in the design after an email merge
I have designed an email in Publisher 2007 that I want to merge with a number of email addresses and other data. The size of the completed email is about 21cm x 53 cm. Everything works perfect until after the merge. When I open some of the products of the email merge (before sending them off), I discover that all of them have text and lines missing in the horizontal area between 46 and 48 cm from the top of the document. In that area the merged emails are completely blank I have tried over and over again to solve the mysterious problem, without any success. Please advise. -- Fanie ...

Business Units record seperation
I have an organization with a top level Organization and the 4 subsidiary B U’s. This is all set up in CRM and the CEO can see leads across all BU's. The problem that I am having is that a sales manager can also see all leads through out the organization also ie, leads that they do not own and have not been assigned to them. The sales manager BU is set so that members can only see (read) the leads in their own business unit. Structure – Organization – CEO Business Unit A – sales manager A Business Unit B– sales manager B Business Unit C– sales manager C Business Unit D– sales...

How to print address label from single business card?
I like to print a single mailing label from the Contact record in Outlook. Can I do it from the Business Card. -- G. Vance, Ph.D. Products That Matter.ca Kinder, Safer, Faster Disinfectants Dundas, Ontario "George Vance" <GeorgeVance@discussions.microsoft.com> wrote in message news:42C2EBAF-8A8B-461D-8458-A7CD42B487D2@microsoft.com... > I like to print a single mailing label from the Contact record in Outlook. > Can I do it from the Business Card. > -- > G. Vance, Ph.D. > Products That Matter.ca > Kinder, Safer, Faster Disinfectants >...

Money 05 small business to 06?
Hi, I have money05 small business version. I would like to downgrade to premium. When I have done the same from 04 to 05 I have file corruption that made me stick with 05 small business. ANy idea if I can do this with 06 version. In microsoft.public.money, alexasha wrote: >Hi, I have money05 small business version. I would like to downgrade to >premium. When I have done the same from 04 to 05 I have file corruption that >made me stick with 05 small business. ANy idea if I can do this with 06 >version. Make a copy of your file, install the downloadable trial, convert the co...

How to add more columns in Business Portal?
Hi, In BP 2.5, BP -> Purchasing -> Requisitions -> select a vendor, then I got data in nine columns: Item Number, Descriptrion, Required By, Price, Quantity, Unit, Extended Price, Requisition ID, and Site. How can I add one more column, Created By, into it? What permission do I need to have to do it? Thanks for your attention and help. stien ...

windows XP 64 bit service pack
Where can I find the windows 64 bit XP latest service pack? You have replies to your first posting. Ernie wrote: > Where can I find the windows 64 bit XP latest service pack? ...

Lookup to a Custom Asset Register in Service Activity
Hi All, New to CRM just getting to grips with customization. Have created a new Entity called "Asset Database". When anyone creates a service activity, I want to be able to add a lookup field that will allow us to link back to the record in the Asset Database. I have created other lookups from Asset Database to Companies and this worked fine. Cannot get this to work at all. Any ideas ? -- David Dalton IT Systems Ltd Killaloe, Co. Clare, Ireland Microsoft Certified Partner ...

Front End Services Question
I was going through one of my books and came across advise that Exchange Management, MTA Stack, and Routing Engine should be set to disabled on then front end server. I had never heard of this. Has anyone else? Fred T2 http://www.microsoft.com/technet/prodtechnol/exchange/guides/E2k3SecHardGuide/e466094f-c287-4498-aa2b-8845d107f8f3.mspx -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "FredT2" <Fredt2@mail.com> wrote in message news:%23OJ5hSZAGHA.3496@TK2MSFTNGP11.phx.gbl... >I was going through one of m...

Money Deluxe & Business 2003 invoice question
Is there a way to create an invoice for an assembled product that is made up of multiple inventory items? I want to invoice for something like a car, which is made of inventory items of: engine, tires, body, etc. Rather than having all the individual items on the invoice. Thanks. -Derek derek@computinginnovations.com ...

Money 2003, 2004, 2005 won't actually work for small business
I have been using Money Small Business since 2000. Invoicing is a key to most small businesses. 20 invoices per day is not an unusual amount, but when you add that up over a couple of years it becomes way too many invoices to wade through each time you need to look up an invoice, plus everything becomes extremely slow and unusable because of the speed. Support claims there is no way, and no work around, for archiving invoices. And deleting them is not only very time consuming one-at-a-time, but if you did you would have balances all screwed up all over the place (cash account is lin...

I need a large print business card template.
I'm legally blind and often need to share my basic info, name, address, phone number, and e-mail address with other visually people. I need a business card template with no graphics or logo that would have large print; maybe 18-pt for the name and the rest in 14-pt Arial font. Thank you in advance for any help you can provide to this beginning user! Will you need the standard size business card? Do you have the business card stock or will you be using a special size and manually cutting. What version Publisher are you using? In file, page setup, select business card. You will hav...

explorer 5.5 service pack 1
I have been unable to open Money because it askes for service pack 1. 1. I have tried downloading pack 2.0, but it can not be installed. 2. I have tried downloading 6.0, but the instillation process stops asking that other programs be closed (even though all programs have bee closed) A. How can I resolve the downloading problems? B. How can I open money without service pack 2.0? ...

Money 2000 Business & Personal conversion
Hello - can Money 2004 Standard convert a 2000 Business & Personal file? I'm getting an error message: "Money cannot locate '%1' or cannot open it, possibly because it is a read-only file or you do not have permission to change it or your disk drive is write protected." I've checked, and neither is read-only Thanks in advance Mark In microsoft.public.money, Mark LaGrange wrote: >Hello - can Money 2004 Standard convert a 2000 Business & >Personal file? I'm getting an error message: > >"Money cannot locate '%1' or cannot...

Pivot Table Design
Hello, I am working on learning Pivot Tables and wanted some advice. I would like to use the Pivot Tables to work with state testing data. Fields that I would be working with would include: Student Name Student Grade Level Race Special Ed (Y or N) English Language Learner (Y or N) Free and Reduced Meals (Y or N) Math Level (1, 2, or 3) Math Score (a 3-digit raw score) Reading Level (1, 2, or 3) Reading Score (a 3-digit raw score) I would like to be able to generate reports that would show the students in a race that scored at a certain level, or students in a race, who are free and re...

next business day
I have a sheet that calculates expiration dates on loans. They ar entered as 7, 15, 30 days, etc. However, sometimes the expiration dat is a Saturday or Sunday. I need a way to have it bump 2 days if it is Saturday, 1 day if it is a Sunday. Below is the formula I am currentl using: =IF(B4=120,"120 day lock, exp:"&TEXT(D11,"mm/dd/yy (ddd)")&" sen confirm to:"&$A$1&" @"&$C$1&" ph: "&$B$1,"") (d11 is the expiration date destination cell) Please help if you can....Thanks Vato Loc -- Message posted from ht...

converting quickbooks files to money 2004 small business
can it be done? if so, any helpwould be appreciated. ...

schema from web services
I have this 3rd party web services link and I want to use it. I also need schema for the site. I was wondering if there is a way to generate it from my side rather ask site owner to send me one? with schema I meant the one for all the returned xml. "JohnAD" <noemail@this.com> wrote in message news:#WWbWs1MHHA.1240@TK2MSFTNGP03.phx.gbl... >I have this 3rd party web services link and I want to use it. I also need >schema for the site. I was wondering if there is a way to generate it from >my side rather ask site owner to send me one? > > > xsd.exe...

resetting design templates to defaults?
hello, Somehow, the publication designs have changed, you have the "publication designs, but when I bring them into the document (or start it) they all have a green whether font or text box backgrounds. Is there a way to reset the design schemes back to there defaults? thans What version Publisher? Look at the Personal Information, change the default color scheme to Bluebird. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bruce" <notreally@idontwantoo.com> wrote in message news:eJUt9MIeIHA.5240@TK2MSFT...

Can I create a custom control with a designer like a form designer
Using Visual Studio 2008, is there anyway I can create a custom control which is comprised of several standard windows controls put together in a certain way? For example, I want to have a panel, and inside this panel will be a few other Panels with other controls within these panels. I want to position them, set their docking and create a basic interface which would be grouped together as one component. Then I would want to go back to my main app and be able to add these components throughout my app - just drag n drop in on some location set it's docking and be done,...

Windows 2003 terminal services
Hi Has anybody installed CRM 1.2 outlook 2003 client on Windows 2003 terminal services? We are running the main CRM program on a separate server. The Outlook client does not support a multi-profile installation. It can only be installed for 1 profile on the machine. Matt Parks ---------------------------------------- ---------------------------------------- On Sun, 4 Apr 2004 12:13:55 +1000, "John Koziaris" <john@alltasks.com.au> wrote: Hi Has anybody installed CRM 1.2 outlook 2003 client on Windows 2003 terminal services? We are running the main CRM program on a se...