Re: multiple instances of tables in relationship window

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2B7D22B0-6298-474F-8447-C4D902D5B8DA@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2B6AEE67-2C66-4C4B-B7F4-02B15D112134@xxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: multiple instances of tables in relationship window
    ... Your Persons table should appear in the relationships window four times. ... be an agent and an agent can be both a BuyerAgent and a SellerAgent. ... For the Buyer and Seller, the RowSource should be something like this: ... Then I create the Sales table. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiple instances of tables in relationship window
    ... 4- You wrote SellerFK, BuyerFK, SellerAgentFK, BuyerAgentFK all in sales ... Is that mean you have a separate table for seller, buyer, etc.? ... "Persons" with an autonumber primary key and all the information pertaining ... that person is an agent. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiple instances of tables in relationship window
    ... 5- seller agent table ... there there are multiple properties in the database). ... The relationships window ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiple instances of tables in relationship window
    ... ONE of each agent, so the Sales table is on the *many* side of each of those ... Is that mean you have a separate table for seller, buyer, etc.? ... In your relationships window you will see the Persons table four times, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiple instances of tables in relationship window
    ... Agent, etc. to this field. ... Then I create the Sales table. ... create different fields for Buyer, Seller, Agent, etc. ... In your relationships window you will see the Persons table four times, ...
    (microsoft.public.access.tablesdbdesign)