Problem designing tables

M

Moe

Say you own a lawn mowing business and you want to keep track of your
customers. You divide your working territory by areas. So I need to setup a
database that can keep track of a territory #, all the homes I would work on
that territory, and when they where worked on.

Can anyone tell me how to set up relationships to be able to do this?
Thanks in advance
 
J

John Vinson

Say you own a lawn mowing business and you want to keep track of your
customers. You divide your working territory by areas. So I need to setup a
database that can keep track of a territory #, all the homes I would work on
that territory, and when they where worked on.

Can anyone tell me how to set up relationships to be able to do this?
Thanks in advance
Click to expand...

As always, you need to identify your Entities and their relationships.
The Entities I see here are Areas related one to many to Customers, in
turn related one to many to Visits (or jobs, or whatever you want to
call them). Something like:

Areas
AreaID <Primary Key - autonumber or coded name if you have one>
Description

Customers
CustomerID <Primary Key - autonumber>
AreaID <link to Areas>
LastName
FirstName
<other contact information>

Jobs
JobID <Primary Key - autonumber>
CustomerID <who you worked for>
JobDate
<other fields about this visit>


John W. Vinson[MVP]
 
M

Moe

--
Moe


John Vinson said:
As always, you need to identify your Entities and their relationships.
The Entities I see here are Areas related one to many to Customers, in
turn related one to many to Visits (or jobs, or whatever you want to
call them). Something like:

Areas
AreaID <Primary Key - autonumber or coded name if you have one>
Description

Customers
CustomerID <Primary Key - autonumber>
AreaID <link to Areas>
LastName
FirstName
<other contact information>

Jobs
JobID <Primary Key - autonumber>
CustomerID <who you worked for>
JobDate
<other fields about this visit>


John W. Vinson[MVP]
Click to expand...

Ok... that seems like it would work... but just a question... Would the
table Jobs here be related in any way to any other table (maybe customers)?
or how would this work.?
 
J

John Vinson

Ok... that seems like it would work... but just a question... Would the
table Jobs here be related in any way to any other table (maybe customers)?
or how would this work.?
Click to expand...

Sure. There'd be a CustomerID in the Jobs table, to indicate who the
job was for. You could use a Form based on Customers, with a subform
based on Jobs to link the data in the two tables, using CustomerID as
the master/child link field.

John W. Vinson[MVP]
 
Top