Tables and what not...

[Guest]

Hi, Im going to endeavour to explain this the best i possibly can, any help
much appreciated.!

I have a 'Jobs' table that contains information about specific jobs. related
to this i have a 'customer' database. In the customer database i have
information about the company who's job it is for and their contact
details... i didnt realise that sometimes we do more than one job for a
compnay and they have different clients. So basically I need to be able to
keep the link between the job table and cust no. with company name, then link
that to the rest of their details i guess... so that in the job there can be
many job numbers with one compnay name, but other details. The big problem is
that there is data in the database already, any ideas?

 

[kingston via AccessMonster.com]

Here's one way to structure the data:

Jobs {JobID - PK, CoID - FK} so many jobs can belong to the same company, but
one job cannot belong to more than one company
Company {CoID - PK}
Customer {CustID - PK, CoID - FK} assuming each customer is only matched with
one company; otherwise, create a linking table

I think that this will cause the least amount of modification to your current
data structure since you already have the first table (possibly without the
CoID FK) and some incarnation of the second table.

When you have the data structure with the keys set up, just create your query
joining the three datasets and you'll be able to pull customer data based on
the job. HTH