Relationships

[bogglebeats]

hello. i have a form called Clients and a subform within that called Counselling session. The Clients form is linked to a table called Clients and the Counselling session form is linked to a table called Session. One client can have many counselling sessions. I have established the relationship as a one-to-many and have declared the Client ID, Client Last Name, and Client First Name fields from the CLIENTS table to be the basis of the relationship. I have created the same fields in the SESSION table and linked them up using the relationship.

The problem is this. Only Client ID (From CLIENTS Table) gets displayed in the Session Table. Last name and First Name fields dont show up. Is this because Client ID is autoincremented is the original table? Do i need to enforce referential integrity? How do i get last name and first name from the clients table to show up in the sessions table??

ameen

 

[casey]

I'm not entirely sure of your table design, but I wonder what you mean when you say...

"I have established the relationship as a one-to-many and have
declared the Client ID, Client Last Name, and Client First Name fields from the CLIENTS table to be the basis of the relationship."

Your relationships should be between two fields which are "basically" the same key in your two tables. Your ClientID(autonumber field) in your CLIENTS table should be joined to a matching ClientID (number field) in your SESSION table. Also, your Client Last Name and Client First Name should be in your Client table only. If this is the case, then I'm sorry for misunderstanding your question. If not, your table structure is suffereing from design flaws.
To view the Client Last Name and Client First Name as apart of your SESSION table you should create a query joining the two tables w/ the fields you want to see.
You could also alter your SESSION table's ClientID field w/ a query using Lookup tab on that field in table design view. You can change the lookup to a query that shows you Client Last Name and Client First Name and ClientID, if you wish, where ClientID currently resides. I don't recommend doing this though.

 

[bogglebeats]

"To view the Client Last Name and Client First Name as apart of your SESSION table you should create a query joining the two tables w/ the fields you want to see"

-if i do this, then should i create the Client First name and Client Last name fields in the SESSION table or will the query take care of that?


earlier, i had based my relationship on 3 fields (id, fname, and lname), however only one (client id) was showing up in session table. now i am going to try the query method. thanks.

 

[casey]

A 1 to many relationship is a relationship between associated tables (One Client can have many Sessions). These relationships are linked by way of one key only (ClientID). By including ClientID in the SESSIONS table, you can obtain any information about a particular Client from the CLIENT table by way of ClientID. i.e. Do not include Client First name and Client Last name in your SESSIONS table. As a developer one of your goals should be to create the least amount of redundant data in your tables as possible. Putting Client First name and Client Last name in the SESSIONS table creates redundant data; i.e. bad idea.

As I tried to convey previously, Client First name and Client Last name fields should be in your CLIENT table ONLY, but ClientID should exist in both of your tables. ClientID will allow you to join the two tables on that field in your query (In essence, you can obtain Client First name and Client Last name from CLIENT table from ClientID in your SESSIONS table (query)). Because ClientID exists in the SESSIONS table, by way of a query, you can obtain Client First name and Client Last name. As you put, the query takes care of that.

I hope that helps.

 

[bogglebeats]

when i try to view the datasheet view the query i am getting problems. i used the query wizard and the last step asks me to open the query in design view or datasheet view. when i choose datasheet it says "The wizard is unable to open the query in datasheet view, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" so i hit ok. then when i try to click datasheet view manually from design view i get "Type mismatch in expression" why is this??

 

[casey]

Perhaps you are joining two fields that aren't the same type. If you are trying to join CLIENT.ClientID w/ SESSIONS.ClientID, they must be of a "similar" type (i.e. Autonumber and Number) If say one is a Text and the other is a Number, you may have problems).

I suggest you read the previous post I submitted and set up your tables the way I suggested. If you try to link the tables the way you suggested earlier (w/ 3 fields linked), may be causing problems too.

 

[bogglebeats]

ok yes. i needed to change my primary key to a number. thanks for the tip. peace.


ameen