business_table relating to 2 copies of entity_table

[Francis]

Ok, i have the following data structure:

business_table:
pk: businessID
business_name
buyerID (links to entity_table)
sellerID (links to entity_Table_1)

entity_table:
pk: entityID
entity_name
buyerID
sellerID

my question is, how do i change the name of entity_Table_1 to
seller_Table in Relationships interface, so i can easily understand and
read the structure.

I think in Relationships you don't change the name of copies or
instances of the same table (used a lot in self joins), you do this in
Query's interface.

Would like some comments upon this (btw thanks Allen for your example
with pedigrees in http://allenbrowne.com/ser-06.html).

Comments and Answers are appreciated thanks.

Francis
(Portugal)

 

[John Vinson]

Ok, i have the following data structure:

business_table:
pk: businessID
business_name
buyerID (links to entity_table)
sellerID (links to entity_Table_1)

entity_table:
pk: entityID
entity_name
buyerID
sellerID

my question is, how do i change the name of entity_Table_1 to
seller_Table in Relationships interface, so i can easily understand and
read the structure.

I think in Relationships you don't change the name of copies or
instances of the same table (used a lot in self joins), you do this in
Query's interface.

I don't understand your structure. You have a BuyerID and a SellerID
in both tables, AND an EntityID in the Entity table. What is the
purpose of the BuyerID and SellerID fields in the Entity_Table?

As far as I know you cannot change the alias assigned in the
Relationships window (someone might be able to enlighten both of us on
how to do so), but you don't really need to; you can assign an alias
in a Query:

SELECT Business_Table.BusinessName, SellingEntity.Entity_Name AS
Seller, Buying_Entity.EntityName AS Buyer
FROM (Business_Table INNER JOIN Entity_Table AS SellingEntity
ON Business_Table.SellerID = SellingEntity.EntityID)
INNER JOIN Entity_Table AS BuyingEntity
ON Business_Table.SellerID = BuyingEntity.EntityID;


John W. Vinson[MVP]

 

[Francis]

Thank you for your contribution John, in fact that structure is
redundant, and i posted it quite quickly and should give a more
carefull look in my post. I would find that the table structure im
looking forward is:

business_table:
pk: businessID
business_name
buyerID (links to entity_table)
sellerID (links to entity_Table_1)


entity_table:
pk: entityID
entity_name


The problem is that i want a Form that displays for any given entity
(doens't matter if it is a seller or buyer), list of businesses, she is
envolved with, (subform with business_table recordsource?) and controls
with buyerID and sellerID.

 

[John Vinson]

Thank you for your contribution John, in fact that structure is
redundant, and i posted it quite quickly and should give a more
carefull look in my post. I would find that the table structure im
looking forward is:

business_table:
pk: businessID
business_name
buyerID (links to entity_table)
sellerID (links to entity_Table_1)


entity_table:
pk: entityID
entity_name


The problem is that i want a Form that displays for any given entity
(doens't matter if it is a seller or buyer), list of businesses, she is
envolved with, (subform with business_table recordsource?) and controls
with buyerID and sellerID.

The simplest way that comes to mind is to have a Form based on
EntityTable with TWO subforms, both based on BusinessTable; for one
subform use EntityID as the Master Link Field, BuyerID as the Child;
for the other use SellerID as the child.

Or, you could base the subform on a UNION query

SELECT Business_Name, "Seller" As Action, SellerID As Linker
FROM Business_Table
WHERE SellerID IS NOT NULL
UNION ALL
SELECT Business_Name, "Buyer", BuyerID
FROM Business_Table
WHERE BuyerID IS NOT NULL

and use Linker as the Child Link Field. This subform won't be
updateable, though!

John W. Vinson[MVP]

 

[Francis]

It's sorted thanks.

Francis