Design for a service business

[jayC]

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.

 

[Daniel Pineault]

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.

 

[John W. Vinson]

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]