Basic Help Please-relationship troubles! (1 Viewer)

[Franky G]

Hi all,

I have three tables, Property, Owner and LetType

For each property, there can be multiple owners.
For each owner, there can be multiple letter types sent on different dates. The letter types will be limited to 3 or 4 types.

I need to record when we sent the letters to each owner, the date of responses (if any) from each owner and the action we propose to take, on the property as a whole…(not per owner)

I’m struggling with relationships and which fields should be in which tables.
I’m guessing also when to define a primary key and when not to, therefore my relationships are not working the way I want.

I’m trying to use a form to display ( and input) the Property, with Owner and LetType sub-forms.

Can anyone help with how to set up my tables and relationships so that I can record multiple owners and when letters were sent out to these owners….specifically, I don’t know which table the LetDate field(s) should be in.

Thanks for any help on this,

Franky G

 

[Fizzio]

Each property can have many owners but can many owners have a share in many properties??
If not, try this

tblProperty
--------------
PropertyID (1-many with owners), (1-many with corresp)
PropertyTypeID (many-1 with PropertyType)
PropertyAddress1
etc related to property

tblOwner
------------
OwnerID (1-many with corresp)
PropertyID (This will be different if 1 owner can have many properties)
OwnerName
etc related to owner

tblCorrespondence
------------------------
CorrenspondenceID
CorrTypeID (If standard correspondence used) (many - 1 with correspondencetype)
OwnerID
PropertyID
CorrDate
ResponseDate
ActionDate
ActionTypeID (many - 1 with actiontype)


LookupTables:

tblPropertyType
tblActionType
tblCorrespondenceType
etc

Should get you started.

 

[Franky G]

Hi..and thanks for that starter.

I've set up the tables but I'm still having a few problems. First, the lookup tables;

LookupTables:

tblPropertyType
tblActionType
tblCorrespondenceType

Am I right in thinking that there only needs to be one field in each table? Thats what I have done, and made each field the primary key....does that sound right?!

More generally, how do I decide which fields in the other tables should be primary keys and which should I set as Autonumber fields? I think It's the Correspondance table I'm having problems with, which has CorrespondanceID, CorrTypeID, OwnerID, PropertyID and ActionTypeID fields. I have set CorrespondanceID as Autonumber and primary key...is this correct?

I think I'm running into problems when my joined fields are of a different Data Type, i.e. PropertyID in tblProperty is an Autonumber field which is linked to PropertyID in tblOwner which is a text field. Aarrgh...this is total trial and error for me right now!
How on earth do I decide which should be primary keys...autonumber fields etc etc? I'm about to break all relationships and try again....

Sorry I really don't understand relationships and primary keys (yet!) so please bear with me on this one.
Thanks,

Franky G

 

[Fizzio]

All ID fields are a number of some sort. In my example, all of these are autonumber and primary key.

PropertyID
OwnerID
CorrespondenceID

LookupTables
PropertyTypeID
CorrespondenceTypeID
ActionTypeID

Each lookup table has 2 fields, an autonumber ID field and a text description.

You create the link between the ID fields (which are now the same data type - Long Integer)

When creating relationships, think logically on the one - many relationship. For example One property type can be present in many properties therefore one - many => tblPropertyTypes - tblProperties

Also one Correspondence type can be present in many correspondences. One - many => tblCorrespondenceType - tblCorrespondence.

Hope this helps. Search here and microsoft for normalisation, they have some good tutorials on the MS site on data structure.