Relationship/Design Help

[twc1001]

The aim of my database is to essentially have two tables.

One with property names in and the other with people that own the property.

One person can own multiple properties.

I have achieved this using a one to many relationship by have a owner coder assigned to each of the property, so when you select that person then all their relevant properties will display.



However,

The challenge i am having is i now want to be able to select from a list of properties and then for text box to display the the person who looks after this property.

So how would i achieve this? I can't make the owner code primary key in the properties table as i have duplicate values..

Would i need a property code as a primary key then assign that to each person as i see fit?

How would the relationship look as i just can't get my head around it.


Thanks in advance.

T

[Dal Jeanis]

It's not a matter of "primary key". It's a matter of coding a simple join.

AS STATED

Assuming you had two tables like this:

tblOwner
  OwnerID (PK)
  OwnerName

tblProperty
  PropertyID
  OwnerID (FK to tblOwner)
  PropertyDecription

Assuming a combo box named cboProperty contains the PropertyID you want, then the SQL could look like

SELECT 
    TO.OwnerName 
FROM 
    tblOwner AS TO 
    INNER JOIN tblProperty AS TP
    ON TP.OwnerID = TO.OwnerID
WHERE 
    PropertyID = [cboProperty];

The exact syntax for the WHERE clause will depend on where you're putting the code. Most often, on a form, you would leave off the where clause and put the requirement in the filter.

MANY TO MANY

Now, your description of your system means that there could never be two owners. If that's the real situation, then you're good. If not, then you should put a linkage table between them and make it many-to-many.

Assuming you had three tables like this:

tblOwner
  OwnerID (PK)
  OwnerName

tblPropertyOwner
  OwnerID (FK to tblOwner)
  PropertyID (FK to tblProperty)
  
tblProperty
  PropertyID
  PropertyDecription

Assuming a combo box named cboProperty contains the PropertyID you want, then the SQL could look like

SELECT 
    TO.OwnerName 
FROM 
    tblOwner AS TO 
    INNER JOIN 
        (tblProperty AS TP 
         INNER JOIN 
         tblPropertyOwner AS TPO
         ON TP.PropertyID = TPO.PropertyID)
    ON TPO.OwnerID = TO.OwnerID
WHERE 
    TP.PropertyID = [cboProperty];

Technically, if you had the property selected, you wouldn't have to JOIN the TP table, since you already have the Key required for the TPO record, but that's a nit.