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" <ace DeleteThis @discussions.microsoft.com> wrote in message
> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
> > 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
> >
> >
>
>

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" <ace.DeleteThis@discussions.microsoft.com> wrote in message
news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@microsoft.com...
> 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" <ace.DeleteThis@discussions.microsoft.com> wrote in message
>> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
>> > 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
>> >
>> >
>>
>>

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" <ace.TakeThisOut@discussions.microsoft.com> wrote in message
> news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@microsoft.com...
> > 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" <ace.TakeThisOut@discussions.microsoft.com> wrote in message
> >> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
> >> > 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
> >> >
> >> >
> >>
> >>
>
>

Hi AC

My answers are in-line...

"ace" <ace RemoveThis @discussions.microsoft.com> wrote in message
news:6E5DD123-EA4F-4B52-A559-A35BA64FCF46@microsoft.com...
> 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" <ace RemoveThis @discussions.microsoft.com> wrote in message
>> news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@microsoft.com...
>> > 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" <ace RemoveThis @discussions.microsoft.com> wrote in message
>> >> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>>
>>

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" <ace.DeleteThis@discussions.microsoft.com> wrote in message
> news:6E5DD123-EA4F-4B52-A559-A35BA64FCF46@microsoft.com...
> > 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" <ace.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@microsoft.com...
> >> > 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" <ace.DeleteThis@discussions.microsoft.com> wrote in message
> >> >> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
> >> >> > 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
>
>
>

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" <ace DeleteThis @discussions.microsoft.com> wrote in message
news:D5699E79-44F1-46A8-A5DF-370D8DEEC267@microsoft.com...
> 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" <ace DeleteThis @discussions.microsoft.com> wrote in message
>> news:6E5DD123-EA4F-4B52-A559-A35BA64FCF46@microsoft.com...
>> > 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" <ace DeleteThis @discussions.microsoft.com> wrote in message
>> >> news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@microsoft.com...
>> >> > 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" <ace DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@microsoft.com...
>> >> >> > 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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>
>>