Indeterminate Relationship

[brucey54]

Hi Folks,

Please could someone explain to me if this is correct!

I have created the following tables;

·Carer Details
·Rota Details
·Client Details

Relationships

TblCarer (1 – M) TblRota

A Carers can have many rotas

TblRota (1 –M) TblClient

A rota can have many clients.

Now to make life easy, I have create some lookup fields within the Rota table i.e. Client names and Carer names. This will allow the user to click on the combo box within the form and select the appropriate names.

Now for the problem - I cannot add more than one client name to the rota sub form.

To manager this I have to set the relationship between the TblRota & TblClient to a indeterminate relationship.

I’m I losing my mind – H.E.L.P

Database attached.

Brucey54

 

[jdraw]

I would not use lookup fields within table definitions.
see http://access.mvps.org/access/lookupfields.htm

You may also find this useful
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip