Re: Many to Many Relationships

From: Lynn (anonymous_at_discussions.microsoft.com)
Date: 07/07/04 

Date: Wed, 7 Jul 2004 11:21:35 -0700

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" <anonymous@discussions.microsoft.com> wrote in
message
>news:286c001c46447$2c9b7e20$a401280a@phx.gbl...
>> 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.
>
>
>.
>

Relevant Pages

  • RE: Creating a quote form
    ... CustomerID AutoNumber (Primary Key ) ... CustomerNumber Number (Foreign Key to Customers ) ... QuoteNumber Number (FK to Quotes) ... To print the report, which will no doubt have the Customer Name, the Product ...
    (microsoft.public.access.forms)
  • More assistance with Many to Many Relationships
    ... up a combined primary key with the primary keys from the ... I need to be able to track all Quotes and there ... I have Customers that may have Quotes and Jobs and I may ...
    (microsoft.public.access.tablesdbdesign)
  • Many to Many Relationships
    ... I am new to Access databases and to date I ... Quotes and Jobs both have sub ... understand correctly will link the Customers with Jobs ...
    (microsoft.public.access.tablesdbdesign)
  • Re: More assistance with Many to Many Relationships
    ... The quote could definitely lead to multiple jobs. ... to link the Customers to both Quotes and Jobs. ... >> I have Customers that may have Quotes and Jobs and I ...
    (microsoft.public.access.tablesdbdesign)
  • Re: More assistance with Many to Many Relationships
    ... between Customers and Jobs, and a many-to-many between Quotes and Jobs, is ... (typically CustomerID) ... >>Designing Relational Database Systems ...
    (microsoft.public.access.tablesdbdesign)