If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to Many Relationships
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. |
#2
|
|||
|
|||
Many to Many Relationships
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. |
#3
|
|||
|
|||
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. . |
#4
|
|||
|
|||
Many to Many Relationships
comments inline.
"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. 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. 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. . |
#5
|
|||
|
|||
Many to Many Relationships
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 -----Original Message----- comments inline. "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. 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. 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. . . |
#6
|
|||
|
|||
Many to Many Relationships
comments inline.
"Lynn" wrote in message ... 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 a 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 -----Original Message----- comments inline. "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. 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. 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. . . |
#7
|
|||
|
|||
Many to Many Relationships
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. "Lynn" wrote in message ... 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 a 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 -----Original Message----- comments inline. "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. 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. 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. . . . |
#8
|
|||
|
|||
Many to Many Relationships
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. -----Original 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Visio Novice - Column attributes and relationships | Bob Pulse | Visio | 3 | July 18th, 2004 06:51 AM |
Not seeing all relationships in layout window | jettabug | General Discussion | 3 | June 18th, 2004 05:42 PM |
relationships | Steven | Database Design | 2 | June 17th, 2004 02:17 PM |
removing relationships. | FFira | General Discussion | 1 | June 4th, 2004 05:33 AM |
Table Relationships Design | Tom | Database Design | 1 | May 5th, 2004 11:56 PM |