Many to Many Relationships

[Lynn]

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.

 

[tina]

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]

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).

 

[tina]

comments inline.

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.

that would be the standard setup for a linking table, yes.

My problem is that each job may not have a Quote and each
Quote may not lead to a job.

okay. then my assumption that "a job is always proceeded by a quote that the
customer accepts", is incorrect. that means that instead of the tblJobs i
posted before, it should be more along the lines of

tblJobs
JobID (primary key)
CustID (foreign key from tblCustomers)
other fields about the job as a whole

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.

yes, that's a little tricky. it seems like a one-to-many relationship: one
job may have many quotes, but each quote can only have one job.
but since a quote can exist independently without any job record (and would
be generated before a job, i imagine), it can't form the child part of the
relationship. so i agree that you would need to treat it as a many to many
relationship and employ a linking table - using the primary key fields from
tblQuotes and tblJobs as a combo primary key.

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.

see above.

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.

easily set up, same format as tblJobDetails.

 

[Lynn]

Thank you Tina, I think this is finally making sense to
me and I am getting confirmation that I am on the right
track. Can you please explain to me exactly how the
linking tables work with the combined primary codes.
Should they have a special name. I think I have them
linked together correctly as one primary key:

The information that shows under the Indexes is:

Line1(Index Name) Customer ID (Field Name) Customer ID
Line2(Index Name) Primary Key (Field Name) Quote ID
Line3 (Index Name) Blank Space (Field Name) Customer ID
Line4 (Index Name) Customer ID (Field Name) Quote ID

Just one more thing for clarification at this time. How
does the linking table affect forms that I will be making
up with Quotes and Jobs. What do I use as the Primary
Key from the linking table? Should this Combined Prinary
Key have a special name? I am not sure about what will
happen to this link for the next step.

I apologize for being ignorant about the workings of
Access but I think the best way to learn is having
something to apply it too.

Thank you so much for your assistance.

Lynn

 

[tina]

comments inline.

Thank you Tina, I think this is finally making sense to
me and I am getting confirmation that I am on the right
track. Can you please explain to me exactly how the
linking tables work with the combined primary codes.
Should they have a special name.

if you mean the fields in the linking table, i would give them unique names.
personally, i never use the same fieldname twice in one database. also, i
like to know exactly what table a field belongs to, so i start all my
fieldnames with the (usually) first letter of the tablename. for instance
tblJobs, would have JID (primary key), JName, etc, etc. also, i keep the
"original name" of all my foreign keys but add a prefix so i know they're
foreign key fields.
so, if i were naming your linking table, i'd call it

tblJobQuotes
JQfkJID (primary key from tblJobs)
JQfkQID (primary key from tblQuotes)

but that's just me. the main thing is to come up with a standard way of
naming all your objects - tables, fields, queries, forms, reports, macros
and modules - and stick with it, be consistent. that way you know what
you're looking at, and so does the next developer who comes along and has to
work on your database. the only "rules" you really need to take to heart
are:
1) don't use Access Reserved words such as Name, Date, etc.
2) don't put spaces or special characters in names; use numbers, letters and
underscores ( _ ) only.

I think I have them
linked together correctly as one primary key:

it's easy to tell. in table design view, the square "record selector" box at
the left of the fieldname has a little key in it to designate the primary
key field. in your linking table, both fields should show the little key.
Access only allows one primary key, by default. so when more than one field
has a key designation, you have one combination primary key - not 2 separate
primary keys.

The information that shows under the Indexes is:

Line1(Index Name) Customer ID (Field Name) Customer ID
Line2(Index Name) Primary Key (Field Name) Quote ID
Line3 (Index Name) Blank Space (Field Name) Customer ID
Line4 (Index Name) Customer ID (Field Name) Quote ID

if those're the indexes the system assigned, then they're probably fine. i
rarely, if ever, change an index assigned by the system.

Just one more thing for clarification at this time. How
does the linking table affect forms that I will be making
up with Quotes and Jobs. What do I use as the Primary
Key from the linking table? Should this Combined Prinary
Key have a special name? I am not sure about what will
happen to this link for the next step.

based on the requirements you specified, you'll need to be able to enter
quotes as independent records, and jobs as independent records - so let's
say separate forms for each of them. based on the idea that a quote comes
before a job, suggest you set up the linking table as a subform of frmJobs.
the form/subform will be linked on the JobID (or JID) field in both tables.
you'll only enter a record in the subform when you want to link a specific
quote record to a specific job record. the JobID will be entered
automatically in the subform record, all you have to do is enter the QuoteID
(QID) - typically you'd use a combo box to show a list of all quotes for the
CustomerID assigned to that job record.

I apologize for being ignorant about the workings of
Access but I think the best way to learn is having
something to apply it too.

i agree; i've never been any good at "theoretical" problems. i think you're
doing well, and i was impressed that you started out by researching tables
and relationships. that was the very best thing you could have done, and so
many people don't do it.
i hope i didn't confuse the heck out of you with any of the above comments;
i'm not nearly as clear and concise as i'd like to be - being much better at
"show" than "tell". if you'd like an example of the setup i described, post
your email address (but beware the spammers), and i'll send one to you.

Thank you so much for your assistance.

you're welcome. hth

 

[Lynn]

Thank you so much Tina> I will work with the information
that you have kindly provided to me and will be back if I
require more information. What you have given to me
helps a lot.

Lynn

-----Original Message-----
comments inline.



if you mean the fields in the linking table, i would give them unique names.
personally, i never use the same fieldname twice in one database. also, i
like to know exactly what table a field belongs to, so i start all my
fieldnames with the (usually) first letter of the tablename. for instance
tblJobs, would have JID (primary key), JName, etc, etc. also, i keep the
"original name" of all my foreign keys but add a prefix so i know they're
foreign key fields.
so, if i were naming your linking table, i'd call it

tblJobQuotes
JQfkJID (primary key from tblJobs)
JQfkQID (primary key from tblQuotes)

but that's just me. the main thing is to come up with a standard way of
naming all your objects - tables, fields, queries, forms, reports, macros
and modules - and stick with it, be consistent. that way you know what
you're looking at, and so does the next developer who comes along and has to
work on your database. the only "rules" you really need to take to heart
are:
1) don't use Access Reserved words such as Name, Date, etc.
2) don't put spaces or special characters in names; use numbers, letters and
underscores ( _ ) only.


it's easy to tell. in table design view, the

square "record selector" box at

 

[Guest]

I have done many databases with many forms and here is a
trick I use to creat forms, etc that I use all the time.
Now mind you, there are some that say this is not the way
to do it, but I have used it for years and have had no
problem. Maybe luck? Don't know, but it works for me.
I create all my tables, and create the links. Then for
the form, I create a query from the created/linked
tables, that contains all the fields from all the
tables. (now if you have extremely large tables, this
probably may not work). The result of the query will be
all the information from all the tables. Then I create
the form with the wizard using this query as my source
and just pick the fields I want. Since I have one query
with all the proper linking, etc., I also use that query
as the source for lots of other queries. Saves me time.
But again, there are some that say you should not do
this. I have never seen the downside of it. If anyone
knows what it is, please let me know. Hope this helps.