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:
