Table Design Brainstorm

[Nick Calladine]

Hi I am starting to design a quotation / invoice system for a friend with
access and need a bit of direction on the way i should create the tables
with effect to the grouping and relationships

The guy is a plumber.

He gets asked to quote for a job > a job record needs to be created

The job record will have a location ,ie address and contact details
The job record will also have a customer

There can be many customer assoicated with one property and many customer
can have many jobs

I keep trying to create a table which is called jobs.

Should i have two seperate table and relationship pointing to jobs
(customer, job location) or should customer be linked to job location or
vise versa...

Thanks

Nick

 

[Vincent Johns]

Hi I am starting to design a quotation / invoice system for a friend with
access and need a bit of direction on the way i should create the tables
with effect to the grouping and relationships

The guy is a plumber.

He gets asked to quote for a job > a job record needs to be created

The job record will have a location ,ie address and contact details
The job record will also have a customer

There can be many customer assoicated with one property and many customer
can have many jobs

So the Table [job records] will contain (among others) a field
containing a foreign key matching the primary key of the record in
[Customers], identifying the (unique, I assume) customer who ordered the
job done.

If you have a lot of information about a job site (property location)
that is shared by several customers, then a [property] Table might be
called for, too, and [Customers] might include a link (foreign key) to
there. But if one customer might order jobs at multiple sites, you'll
probably want to add a Table that will link them -- each record would
include a link to a customer and one to a property location.

If one customer orders jobs at multiple sites only very rarely, you
might get by with duplicating the customer's information (pretending
that his twin brother is at the other location), but it's probably
better to set up the extra table and avoid that difficulty.

I keep trying to create a table which is called jobs.

Should i have two seperate table and relationship pointing to jobs
(customer, job location) or should customer be linked to job location or
vise versa...

Thanks

Nick

Customer can be linked to job location if you're pretty sure he'll never
be anywhere else.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[jahoobob]

I would create a table of customers and a seperate table for jobs.
Assign the customers a unique number and store that with each job.
tblCustomer would have:
CustNo
Name
Address
City
State
Zip
Phone
Contact
etc.

tblJob would contain:
JobNo
CustNo
Address
City
State
Zip
LotNo (for new construction)
Specifications (make this a memo field)
QuoteAmt
QuoteDate
etc.

If you wanted to use this as a billing method also create another table
tblBilling to include
JobNo
Specification (omit from tblJoB)
OuoteAmt (omit from tblJoB)
QuoteDate (omit from tblJoB)
BillingAmt
BillingDate
etc.

I hope this gets you going in the right direction. If you ahve any
further questions, don't hesitate to ask.
Bob

 

[Jeff Boyce]

Bob

An observation ...

Your description of a tblCustomer included a field named "Name". This is a
reserved word in Access, and will only confuse both you and Access.

Moreover, if you'd ever likely want to sort your customers' names, having
FirstName and LastName would make it much simpler to do (and allows for
MailMerge like "Dear John").

Regards

Jeff Boyce
<Office/Access MVP>