More assistance with Many to Many Relationships

Thanks for your assistance Tina. I don't think I
explained myself enough. With what I have read about
many to many relationships, it stated that you had to set
up a combined primary key with the primary keys from the
two tables.

My problem is that each job may not have a Quote and each
Quote may not lead to a job. I initially was going to
set it up this way but it will not work for what we
need. I need to be able to track all Quotes and there
may be many different Quotes pertaining to one job or a
Job may not happern.

I have Customers that may have Quotes and Jobs and I may
have many quotes for one customer and the same with the
jobs. I have tried to think of a way to link the quotes
and jobs but it does not work.

Then to complicate the database even more I have Extra
Work information that needs to tie into the Jobs. This
should only be a one to many relationship.

Thank you for your assistance and I hope this information
better describes my problem.


-----Original Message-----
tblCustomers
CustID (primary key)
other fields about the customer

tblCustQuotes (one-to-many relationship with
tblCustomers)
QuoteID (primary key)
CustID (foreign key from tblCustomers)
other fields about the quote as a whole

tblQuoteDetails (one-to-many relationship with tblQuotes)
QDetailID (primary key)
QuoteID (foreign key from tblCustQuotes)
other fields about the line item of the quote

tblJobs (one-to-one relationship with tblQuotes)
JobID (foreign key from tblQuotes is the primary key
here)
other fields about the job as a whole

tblJobDetails (one-to-many relationship with tblJobs)
JDetailID (primary key)
JobID (foreign key from tblJobs)
other fields about the line item of the job

the above assumes that each quote belongs to one
customer, and that each job
stems from one specific quote that the customer
accepted, which is why
tblJobs is linked to tblQuotes and not directly to
tblCustomers. in a
one-to-one relationship, typically both tables use the
same primary key.

from your description, i don't see a many-to-many
relationship at all. but
maybe i am misunderstanding your concept.

also, i get the impression you don't have a firm grasp
on the meanings of
one-to-many and many-to-many. here's how they work:

one-to-many
one record in tblA may link to many records in tblB,
*but* each record in
tblB only links to one record in tblA.
Orders and OrderDetails is a classic example of one-to-
many.

many-to-many
one record in tblA may link to many records in tblB,
*and* one record in
tblB may link to many records in tblA.
Orders and Products is a classic example of many-to-
many. to express this
relationship, you need a linking table -
tblProductOrders (or OrderDetails).

also, here's one-to-one:
one record in tblA links to only one record in tblB,
*and* one record in
tblB links to only one record in tblA.

remember that to determine the type of relationship, you
have to consider
the link from A to B, *and also* consider the link from
B to A. always look
at "both sides" of the link.

hth


"Lynn" wrote in
message
...
Good morning. I am new to Access databases and to
date I
have done a considerable amount of research and reading
but am unable to ensure the steps I am taking in the
set
up of the tables will work as I design the forms and
reports. I have set up tables for Customers that will
link to Quotes and Jobs. Quotes and Jobs both have sub
tables linking to details that will be used for line
items.

To this point I have inserted two tables for
Customer/Quotes and another for Customer/Jobs which if
I
understand correctly will link the Customers with Jobs
and Quotes. I can have many Customers that may have
many
quotes or many jobs.

I understand how the set up of the tables is vital for
the database to work correctly.

This is the first step and once I know I have done this
correctly I need to understand how I use the tables
created to set up my forms.

Thank you in advance for any assistance you can provide
to help me to go on from this point.


.

..

Lynn,

Can a quote lead to multiple jobs? You say there may be multiple quotes for
a job, but can it lead the other way? If not, all you need is to have an
(optional) job number in the quote file.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Lynn" wrote in message
...
Thanks for your assistance Tina. I don't think I
explained myself enough. With what I have read about
many to many relationships, it stated that you had to set
up a combined primary key with the primary keys from the
two tables.

My problem is that each job may not have a Quote and each
Quote may not lead to a job. I initially was going to
set it up this way but it will not work for what we
need. I need to be able to track all Quotes and there
may be many different Quotes pertaining to one job or a
Job may not happern.

I have Customers that may have Quotes and Jobs and I may
have many quotes for one customer and the same with the
jobs. I have tried to think of a way to link the quotes
and jobs but it does not work.

Then to complicate the database even more I have Extra
Work information that needs to tie into the Jobs. This
should only be a one to many relationship.

Thank you for your assistance and I hope this information
better describes my problem.

Rebecca:

The quote could definitely lead to multiple jobs. I need
to link the Customers to both Quotes and Jobs. I am not
sure I clearly understand how that will work for both
Quotes and Jobs. How do you define Parent and Child
relationships in my situation or should I not go there.

Thank you in advance for your assistance.


-----Original Message-----
Lynn,

Can a quote lead to multiple jobs? You say there may be
multiple quotes for
a job, but can it lead the other way? If not, all you
need is to have an
(optional) job number in the quote file.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Lynn" wrote in
message
...
Thanks for your assistance Tina. I don't think I
explained myself enough. With what I have read about
many to many relationships, it stated that you had to
set
up a combined primary key with the primary keys from
the
two tables.

My problem is that each job may not have a Quote and
each
Quote may not lead to a job. I initially was going to
set it up this way but it will not work for what we
need. I need to be able to track all Quotes and there
may be many different Quotes pertaining to one job or a
Job may not happern.

I have Customers that may have Quotes and Jobs and I
may
have many quotes for one customer and the same with the
jobs. I have tried to think of a way to link the
quotes
and jobs but it does not work.

Then to complicate the database even more I have Extra
Work information that needs to tie into the Jobs. This
should only be a one to many relationship.

Thank you for your assistance and I hope this
information
better describes my problem.


.

Okay, take a deep breath, this is going to get ugly g

Okay, so you have a 1-to-many between Customers and Quotes, a 1-to-many
between Customers and Jobs, and a many-to-many between Quotes and Jobs, is
that correct? If so, then you need to include the PK of the Customer table
(typically CustomerID) in both the Orders and Jobs tables, that will allow
you to track that link either way. Then you need what's called a "junction"
or "resolver" table between the Orders and Jobs tables to resolve the
many-to-many between them. That table only needs the PKs of the Orders and
Jobs tables.

Now, the way this works: If Mr. Smith has two quotes, Q1 and Q2, and Q1
doesn't lead to a Job, but Q2 leads to three jobs, J1, J2 and J3, then your
tables would look like:

Customers
- Mr. Smith, ID: 01

Quotes
- Q1, CustomerID: 01
- Q2, CustomerID: 01

JobQuotes
- Q2, J1
- Q2, J2
- Q2, J3

Quotes
- J1, CustomerID: 01
- J2, CustomerID: 01
- J3, CustomerID: 01

Once that's set up, you can find all the quotes for a customer, all the jobs
for a customer, all the quotes for a job, and all the jobs for a quote using
straightforward queries.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Lynn" wrote in message
...
Rebecca:

The quote could definitely lead to multiple jobs. I need
to link the Customers to both Quotes and Jobs. I am not
sure I clearly understand how that will work for both
Quotes and Jobs. How do you define Parent and Child
relationships in my situation or should I not go there.

Thank you in advance for your assistance.


-----Original Message-----
Lynn,

Can a quote lead to multiple jobs? You say there may be
multiple quotes for
a job, but can it lead the other way? If not, all you
need is to have an
(optional) job number in the quote file.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Lynn" wrote in
message
...
Thanks for your assistance Tina. I don't think I
explained myself enough. With what I have read about
many to many relationships, it stated that you had to
set
up a combined primary key with the primary keys from
the
two tables.

My problem is that each job may not have a Quote and
each
Quote may not lead to a job. I initially was going to
set it up this way but it will not work for what we
need. I need to be able to track all Quotes and there
may be many different Quotes pertaining to one job or a
Job may not happern.

I have Customers that may have Quotes and Jobs and I
may
have many quotes for one customer and the same with the
jobs. I have tried to think of a way to link the
quotes
and jobs but it does not work.

Then to complicate the database even more I have Extra
Work information that needs to tie into the Jobs. This
should only be a one to many relationship.

Thank you for your assistance and I hope this
information
better describes my problem.


.