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
|
|||
|
|||
multiple instances of tables in relationship window
following question was asked in 2005 and there was an answer which I presume
is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#2
|
|||
|
|||
multiple instances of tables in relationship window
Hi AC
This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#3
|
|||
|
|||
multiple instances of tables in relationship window
Hi Graham,
What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#4
|
|||
|
|||
multiple instances of tables in relationship window
Hi AC
If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#5
|
|||
|
|||
multiple instances of tables in relationship window
Hi Graham,
Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#6
|
|||
|
|||
multiple instances of tables in relationship window
Hi AC
My answers are in-line... "ace" wrote in message ... Hi Graham, Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? They are not *real* tables - they are just duplicate views of the same table in the relationships window. They can appear for two reasons: 1. You explicitly go to "Show table..." and add a table that is already there. 2. You have a one-side table that is related to multiple foreign keys in the same many-side table (for example, Persons is related to both BuyerFK and SellerFK). Even if you delete one instance from the relationships window, it will return if you click the "Show all relationships" button. 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? If you delete them and save the relationshis window, they will only come back in condition (2) above, and they will be there for a legitimate reason. 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? NO NO NO NO! For any sale you have ONE buyer, ONE seller, ONE property, and ONE of each agent, so the Sales table is on the *many* side of each of those relationships. 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? No, you don't have buyer/seller/agent tables, because they are all "Persons". You relate Persons.PersonID one-to-many to Sales.SellerFK, and then relate Persons.PersonID one-to-many to Sales.BuyerFK, etc In your relationships window you will see the Persons table four times, as Persons, Persons_1, Persons_2, and Persons_3. This is all fine and correct. 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! You should not have a property address in the Sales table. It belongs in the Properties table. The sale is for one property, identified by the PropertyID value in PropertyFK. If you want to know the address of the propert being sold (or the number of bedrooms, or the building materials, etc) you just follow the link. 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? No, this would mean that a sale can be associated with only one project, and that one project could potentially be related to multiple sales. I should think it's more likely to be the other way around! First, ask yourself: "Is a project always related to a sale (i.e. something that happens as part of the sale process), or can it relate to a property that is not currently being sold?" If it is always related to a sale, then relate Projects (many-side) to Sales (one-side). Projects would include a field named SaleFK. If it is related to a property independent of a sale, then relate Projects (many-side) to Properties (one-side). Projects would include a field named PropertyFK. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#7
|
|||
|
|||
multiple instances of tables in relationship window
Hi Graham,
Thanks. I think I am almost there! I understand that through the relationship window, I need to create the links between Persons table and Sales table. I am not sure how I do for example Persons.PersonID one-to-many to Sales.SellerFK relationship! First I need to create a Persons table which has, let's say, a Category field. This is a text field and using the lookup wizard I enter Buyer,Seller, Agent, etc. to this field. Then I create the Sales table. In this table I create different fields for Buyer, Seller, Agent, etc. Then in the Relationship window, I move PersonID from the Person table to the Sales table Seller field. And I repeat this for Buyer, Agent, etc. Is this correct so far? Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that! Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC My answers are in-line... "ace" wrote in message ... Hi Graham, Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? They are not *real* tables - they are just duplicate views of the same table in the relationships window. They can appear for two reasons: 1. You explicitly go to "Show table..." and add a table that is already there. 2. You have a one-side table that is related to multiple foreign keys in the same many-side table (for example, Persons is related to both BuyerFK and SellerFK). Even if you delete one instance from the relationships window, it will return if you click the "Show all relationships" button. 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? If you delete them and save the relationshis window, they will only come back in condition (2) above, and they will be there for a legitimate reason. 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? NO NO NO NO! For any sale you have ONE buyer, ONE seller, ONE property, and ONE of each agent, so the Sales table is on the *many* side of each of those relationships. 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? No, you don't have buyer/seller/agent tables, because they are all "Persons". You relate Persons.PersonID one-to-many to Sales.SellerFK, and then relate Persons.PersonID one-to-many to Sales.BuyerFK, etc In your relationships window you will see the Persons table four times, as Persons, Persons_1, Persons_2, and Persons_3. This is all fine and correct. 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! You should not have a property address in the Sales table. It belongs in the Properties table. The sale is for one property, identified by the PropertyID value in PropertyFK. If you want to know the address of the propert being sold (or the number of bedrooms, or the building materials, etc) you just follow the link. 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? No, this would mean that a sale can be associated with only one project, and that one project could potentially be related to multiple sales. I should think it's more likely to be the other way around! First, ask yourself: "Is a project always related to a sale (i.e. something that happens as part of the sale process), or can it relate to a property that is not currently being sold?" If it is always related to a sale, then relate Projects (many-side) to Sales (one-side). Projects would include a field named SaleFK. If it is related to a property independent of a sale, then relate Projects (many-side) to Properties (one-side). Projects would include a field named PropertyFK. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#8
|
|||
|
|||
multiple instances of tables in relationship window
Hi AC
Your Persons table should appear in the relationships window four times. Click on the "Show Table" button and double-click Persons 4 times. They will appear as Persons, Persons_1, Persons_2, and Persons_3. To create the relationships, drag PersonID from Persons to BuyerFK and select the option for relational integrity. Then repeat for Persons_1 to SellerFK, Persons_3 to BuyerAgentFK, and Persons_4 to SellerAgentFK. If you haven't already done so then you must also create a relationship between Properties.PropertyID and Sales.PropertyFK. You now have your relationships set up. I would not put a Category field in your Persons table. Let's assume that any Person can be both a buyer and a seller. Also, a subset or Persons can be an agent and an agent can be both a BuyerAgent and a SellerAgent. So all you need is a yes/no field to indicate whether the person is an agent. For the data entry, use combo boxes. For the Buyer and Seller, the RowSource should be something like this: Select PersonID, First Name & " " & LastName from Persons order by FirstName, LastName; For the Buyer/Seller Agent you want the list to include only agents: Select PersonID, First Name & " " & LastName from Persons where Agent 0 order by FirstName, LastName; For each combo box, set the following properties: ColumnCount: 2 BoundColumn: 1 ColumnWidths: 0 (this will hide the left column - PersonID) -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message news Hi Graham, Thanks. I think I am almost there! I understand that through the relationship window, I need to create the links between Persons table and Sales table. I am not sure how I do for example Persons.PersonID one-to-many to Sales.SellerFK relationship! First I need to create a Persons table which has, let's say, a Category field. This is a text field and using the lookup wizard I enter Buyer,Seller, Agent, etc. to this field. Then I create the Sales table. In this table I create different fields for Buyer, Seller, Agent, etc. Then in the Relationship window, I move PersonID from the Person table to the Sales table Seller field. And I repeat this for Buyer, Agent, etc. Is this correct so far? Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that! Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC My answers are in-line... "ace" wrote in message ... Hi Graham, Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? They are not *real* tables - they are just duplicate views of the same table in the relationships window. They can appear for two reasons: 1. You explicitly go to "Show table..." and add a table that is already there. 2. You have a one-side table that is related to multiple foreign keys in the same many-side table (for example, Persons is related to both BuyerFK and SellerFK). Even if you delete one instance from the relationships window, it will return if you click the "Show all relationships" button. 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? If you delete them and save the relationshis window, they will only come back in condition (2) above, and they will be there for a legitimate reason. 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? NO NO NO NO! For any sale you have ONE buyer, ONE seller, ONE property, and ONE of each agent, so the Sales table is on the *many* side of each of those relationships. 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? No, you don't have buyer/seller/agent tables, because they are all "Persons". You relate Persons.PersonID one-to-many to Sales.SellerFK, and then relate Persons.PersonID one-to-many to Sales.BuyerFK, etc In your relationships window you will see the Persons table four times, as Persons, Persons_1, Persons_2, and Persons_3. This is all fine and correct. 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! You should not have a property address in the Sales table. It belongs in the Properties table. The sale is for one property, identified by the PropertyID value in PropertyFK. If you want to know the address of the propert being sold (or the number of bedrooms, or the building materials, etc) you just follow the link. 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? No, this would mean that a sale can be associated with only one project, and that one project could potentially be related to multiple sales. I should think it's more likely to be the other way around! First, ask yourself: "Is a project always related to a sale (i.e. something that happens as part of the sale process), or can it relate to a property that is not currently being sold?" If it is always related to a sale, then relate Projects (many-side) to Sales (one-side). Projects would include a field named SaleFK. If it is related to a property independent of a sale, then relate Projects (many-side) to Properties (one-side). Projects would include a field named PropertyFK. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: Hi AC This often happens when the same one-side table is related to two different foreign key fields in the many-side table. For example, you might have a table "Persons" and another "Organisations". The Organisations table has two fields, "OrgPresident" and "OrgSecretary", both related to PersonID in the Persons table. In this case, you will see two copies of Persons in the relationships window - Persons related to OrgPresident and Persons_1 related to OrgSecretary. This is normal and there is no problem. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... following question was asked in 2005 and there was an answer which I presume is not valid for 2007 version! The second copy of the tables have the suffix *_1. Please reply why and how these tables were created and how to eliminate multiple instances of these tables! (assuming I do not need them) Thanks, AC Erdal ________________________________ I have a db with linked tables. The relationships window shows multiple instances of the master table and it's links to the other tables. The db works fine i.e. queries, reports, etc. Why does the table show up more than once. Is this a sign of redundancy or something else I should take notice of? Is there a way to stop showing the redundant links in the relationships window? Thanks, Sandra G |
#9
|
|||
|
|||
multiple instances of tables in relationship window
Hi Graham,
I did not get your reply to the question; "Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that!" Note that initially I may be entering seller and seller agent to the persons table and the property to the proprety table. Then I need to tell the system how the property and the seller/seller agent are related to? Then, when there is an offer, I enter the buyer and buyer agent. So, how do I tell the system the buyer/buyer agent related to particular property? All these are related to data entry. Thanks, AC "Graham Mandeno" wrote: Hi AC Your Persons table should appear in the relationships window four times. Click on the "Show Table" button and double-click Persons 4 times. They will appear as Persons, Persons_1, Persons_2, and Persons_3. To create the relationships, drag PersonID from Persons to BuyerFK and select the option for relational integrity. Then repeat for Persons_1 to SellerFK, Persons_3 to BuyerAgentFK, and Persons_4 to SellerAgentFK. If you haven't already done so then you must also create a relationship between Properties.PropertyID and Sales.PropertyFK. You now have your relationships set up. I would not put a Category field in your Persons table. Let's assume that any Person can be both a buyer and a seller. Also, a subset or Persons can be an agent and an agent can be both a BuyerAgent and a SellerAgent. So all you need is a yes/no field to indicate whether the person is an agent. For the data entry, use combo boxes. For the Buyer and Seller, the RowSource should be something like this: Select PersonID, First Name & " " & LastName from Persons order by FirstName, LastName; For the Buyer/Seller Agent you want the list to include only agents: Select PersonID, First Name & " " & LastName from Persons where Agent 0 order by FirstName, LastName; For each combo box, set the following properties: ColumnCount: 2 BoundColumn: 1 ColumnWidths: 0 (this will hide the left column - PersonID) -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message news Hi Graham, Thanks. I think I am almost there! I understand that through the relationship window, I need to create the links between Persons table and Sales table. I am not sure how I do for example Persons.PersonID one-to-many to Sales.SellerFK relationship! First I need to create a Persons table which has, let's say, a Category field. This is a text field and using the lookup wizard I enter Buyer,Seller, Agent, etc. to this field. Then I create the Sales table. In this table I create different fields for Buyer, Seller, Agent, etc. Then in the Relationship window, I move PersonID from the Person table to the Sales table Seller field. And I repeat this for Buyer, Agent, etc. Is this correct so far? Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that! Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC My answers are in-line... "ace" wrote in message ... Hi Graham, Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? They are not *real* tables - they are just duplicate views of the same table in the relationships window. They can appear for two reasons: 1. You explicitly go to "Show table..." and add a table that is already there. 2. You have a one-side table that is related to multiple foreign keys in the same many-side table (for example, Persons is related to both BuyerFK and SellerFK). Even if you delete one instance from the relationships window, it will return if you click the "Show all relationships" button. 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? If you delete them and save the relationshis window, they will only come back in condition (2) above, and they will be there for a legitimate reason. 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? NO NO NO NO! For any sale you have ONE buyer, ONE seller, ONE property, and ONE of each agent, so the Sales table is on the *many* side of each of those relationships. 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? No, you don't have buyer/seller/agent tables, because they are all "Persons". You relate Persons.PersonID one-to-many to Sales.SellerFK, and then relate Persons.PersonID one-to-many to Sales.BuyerFK, etc In your relationships window you will see the Persons table four times, as Persons, Persons_1, Persons_2, and Persons_3. This is all fine and correct. 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! You should not have a property address in the Sales table. It belongs in the Properties table. The sale is for one property, identified by the PropertyID value in PropertyFK. If you want to know the address of the propert being sold (or the number of bedrooms, or the building materials, etc) you just follow the link. 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? No, this would mean that a sale can be associated with only one project, and that one project could potentially be related to multiple sales. I should think it's more likely to be the other way around! First, ask yourself: "Is a project always related to a sale (i.e. something that happens as part of the sale process), or can it relate to a property that is not currently being sold?" If it is always related to a sale, then relate Projects (many-side) to Sales (one-side). Projects would include a field named SaleFK. If it is related to a property independent of a sale, then relate Projects (many-side) to Properties (one-side). Projects would include a field named PropertyFK. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: |
#10
|
|||
|
|||
multiple instances of tables in relationship window
Hi AC
Everything in my post from "I would not put a Category field in your Persons table..." on was the answer to that part of your question. You use combo boxes to select a person (any person) for each of the buyer and the seller, and a person marked as an agent for the buyer/seller agents. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, I did not get your reply to the question; "Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that!" Note that initially I may be entering seller and seller agent to the persons table and the property to the proprety table. Then I need to tell the system how the property and the seller/seller agent are related to? Then, when there is an offer, I enter the buyer and buyer agent. So, how do I tell the system the buyer/buyer agent related to particular property? All these are related to data entry. Thanks, AC "Graham Mandeno" wrote: Hi AC Your Persons table should appear in the relationships window four times. Click on the "Show Table" button and double-click Persons 4 times. They will appear as Persons, Persons_1, Persons_2, and Persons_3. To create the relationships, drag PersonID from Persons to BuyerFK and select the option for relational integrity. Then repeat for Persons_1 to SellerFK, Persons_3 to BuyerAgentFK, and Persons_4 to SellerAgentFK. If you haven't already done so then you must also create a relationship between Properties.PropertyID and Sales.PropertyFK. You now have your relationships set up. I would not put a Category field in your Persons table. Let's assume that any Person can be both a buyer and a seller. Also, a subset or Persons can be an agent and an agent can be both a BuyerAgent and a SellerAgent. So all you need is a yes/no field to indicate whether the person is an agent. For the data entry, use combo boxes. For the Buyer and Seller, the RowSource should be something like this: Select PersonID, First Name & " " & LastName from Persons order by FirstName, LastName; For the Buyer/Seller Agent you want the list to include only agents: Select PersonID, First Name & " " & LastName from Persons where Agent 0 order by FirstName, LastName; For each combo box, set the following properties: ColumnCount: 2 BoundColumn: 1 ColumnWidths: 0 (this will hide the left column - PersonID) -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message news Hi Graham, Thanks. I think I am almost there! I understand that through the relationship window, I need to create the links between Persons table and Sales table. I am not sure how I do for example Persons.PersonID one-to-many to Sales.SellerFK relationship! First I need to create a Persons table which has, let's say, a Category field. This is a text field and using the lookup wizard I enter Buyer,Seller, Agent, etc. to this field. Then I create the Sales table. In this table I create different fields for Buyer, Seller, Agent, etc. Then in the Relationship window, I move PersonID from the Person table to the Sales table Seller field. And I repeat this for Buyer, Agent, etc. Is this correct so far? Then, when I am entering the data how do I enter which Buyer, Seller, etc. belongs to Which Sales? One way I can think of is to setup the Sales table so that Seller field can be filled by a pulldown list of Sellers from the Persons table! Is that correct? I am not sure how you can do that! Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC My answers are in-line... "ace" wrote in message ... Hi Graham, Your explanation is great! That helps. I do have couple questions; 1- why _1 tables are generated and how can I avoid creating them? They are not *real* tables - they are just duplicate views of the same table in the relationships window. They can appear for two reasons: 1. You explicitly go to "Show table..." and add a table that is already there. 2. You have a one-side table that is related to multiple foreign keys in the same many-side table (for example, Persons is related to both BuyerFK and SellerFK). Even if you delete one instance from the relationships window, it will return if you click the "Show all relationships" button. 2- These _1 tables show up in relationship page only. Do not show up in tables list. So if I delete them from the relationship page, they would not come back later to relationship page again, right? If you delete them and save the relationshis window, they will only come back in condition (2) above, and they will be there for a legitimate reason. 3- Based on your description, sales table is the one side and all other tables are in the many side! Is that correct? NO NO NO NO! For any sale you have ONE buyer, ONE seller, ONE property, and ONE of each agent, so the Sales table is on the *many* side of each of those relationships. 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales table. Is that mean you have a separate table for seller, buyer, etc.? If you have one table as persons and select a field for buyer/seller/agent, etc. does it mean personsFK? No, you don't have buyer/seller/agent tables, because they are all "Persons". You relate Persons.PersonID one-to-many to Sales.SellerFK, and then relate Persons.PersonID one-to-many to Sales.BuyerFK, etc In your relationships window you will see the Persons table four times, as Persons, Persons_1, Persons_2, and Persons_3. This is all fine and correct. 5- If for some reason I have a duplicate of a field on two tables, should I be creating a one to one relation between them? Like having property address in both property table and in sales table! You should not have a property address in the Sales table. It belongs in the Properties table. The sale is for one property, identified by the PropertyID value in PropertyFK. If you want to know the address of the propert being sold (or the number of bedrooms, or the building materials, etc) you just follow the link. 6- Project table contains projects to be done for the property, like inspections, repairs, etc. I assume I need to have the ProjectFK in sales table as well, right? No, this would mean that a sale can be associated with only one project, and that one project could potentially be related to multiple sales. I should think it's more likely to be the other way around! First, ask yourself: "Is a project always related to a sale (i.e. something that happens as part of the sale process), or can it relate to a property that is not currently being sold?" If it is always related to a sale, then relate Projects (many-side) to Sales (one-side). Projects would include a field named SaleFK. If it is related to a property independent of a sale, then relate Projects (many-side) to Properties (one-side). Projects would include a field named PropertyFK. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Thanks, AC Erdal "Graham Mandeno" wrote: Hi AC If table_1 shows no relationships, you can simply delete it from the Relationships window. Just click on it and press Delete. Even if it *has* relationships, you can still delete it and it won't affect the relationships at all. Now, about your design: First, a table should never contain any value that is not a direct attribute of that table's natural primary key. A seller might have a "home address" or a "postal address", but not a "property address". That should be an attribute of (and therefore a field in) the Properties table. What if a seller is selling two or three properties? Also, a seller could surely also be a buyer could they not? And can't agents buy and sell their own properties? So I would recommend a table of "Persons" with an autonumber primary key and all the information pertaining directly to a person - FirstName, LastName, contact details (address(es) phone number(s), email, etc), and also a yes/no field to indicate whether that person is an agent. Now, can't a property be bought and sold more than once? And the buyer/seller/agents would most likely be different for different sales? So those links to people do not belong in the Properties table. All you want in "Properties" is a primary key, the property address, description, and (maybe) the current owner (this would be a foreign key field related to the Persons table). Then you want a Sales table - SaleID (autonumber), PropertyFK, SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK, DateOnMarket, DateSold, Price, etc... All the "FK" fields are foreign keys for relationships to other tables (Properties for PropertyFK, Persons for the others). I'm not sure what "Projects" are. Does this get you some way down the design path? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "ace" wrote in message ... Hi Graham, What if the table_1 does not show any relationships! What is that mean and if and how we can delete them? My main problem for me is figuring out how the relationships should be setup! For example for a real estate application you have; 1- propety table 2- buyer table 3- buyer agent table 4- seller table 5- seller agent table 6- project table What should be the appropriate relationship look like? Do we consider all tables as one side and property table as many side? For each property there is only one buyer,seller & buyer/seller agent but multiple projects (note there there are multiple properties in the database). For each buyer & seller there is only one property & buyer/seller agent. On the other hand for each agent there are multiple buyers/sellers/properties. What would be the recommended relationships for these? Also, lets say that we have the same field repeated in two tables, like "property address" in seller table and property table. Is that mean we have a one to one relationship between those two tables for the "property address" field? Thanks for your help. AC Erdal "Graham Mandeno" wrote: |
Thread Tools | |
Display Modes | |
|
|