multiple instances of tables in relationship window

[ace]

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

 

[Graham Mandeno]

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.

 

[ace]

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

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

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

 

[Graham Mandeno]

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

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

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

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

 

[ace]

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

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

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

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

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

 

[Graham Mandeno]

Hi AC

My answers are in-line...

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

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

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


:

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

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

 

[ace]

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

Hi AC

My answers are in-line...

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

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

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


:

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

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

 

[Graham Mandeno]

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

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

Hi AC

My answers are in-line...

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

:

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

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


:

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

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

 

[ace]

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

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

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

Hi AC

My answers are in-line...

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

:

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

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:

 

[Graham Mandeno]

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

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

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

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

:

Hi AC

My answers are in-line...

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

:

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

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: