Beginner's relationship problem

[duckster]

Here's the situation: I'm trying to relate two tables, a 1) CLIENT table, and an 2) INCOME table. I have made the primary key in CLIENT table as an autonumber (named CliendID). The INCOME table has ClientID and TaxYear as the primary key (two fields that form the primary key b/c each client can have different income in the respective tax year).

When I add a new record in the CLIENT table, I can't get the INCOME table to automatically update with the CLIENT table's new ClientID. Even after updating a new record in CLIENT table, the INCOME table still shows no new records.

I've tried enforcing referential integrity by going to the design of the INCOME table, opening the RELATIONSHIPS of this table, and adding joining the ClientID of the CLIENT table to the ClientID of the INCOME table, then selecting "ENFORCE REFERENTIAL INTEGRITY" and the cascading options.

Sorry if this seems silly, I'm a newbie to Access. Help appreciated =)

 

[The_Doc_Man]

Your problem is because you were looking at it from the wrong direction. You are correct to enforce relational integrity and from your discussion, you chose the right direction of enforcement. But you misunderstand what it means in practical terms.

Relational integrity would prevent you from entering an income entry before you defined the client. Without a client, there can be no income. But there is nothing to prevent you from entering a client before you enter an income. Because the client is the parent of the relationship. Parents come before children. (That's why the parent/child paradigm is used - it is a "natural" leap of concept.)

You need to look in the help files about parent/child tables and parent/child forms (main/subforms). The Client would be your parent in either case. The income entry would be the child/sub entry.

All that relational integrity does is prevent you from defining things in the wrong order. But you are still required to define them both. You should be able to enter an income record for a given client once you have that client.

If you have a form set up (parent/child form setup), look up the topic on how to link parent and child forms. There is a property in a sub-form that defines the name of the field in the parent and in the child on which the link is based. Which in your case is probably just your client ID number.

 

[duckster]

Thanks, I understand more now. I followed the instructions below on relating tables, but still have a problem. First, here are the instructions I've followed.

Close any tables you have open. You can't create or modify relationships between open tables.
Press F11 to switch to the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.).
Click Relationships on the toolbar.
If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed.
If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.

Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.

Drag the field that you want to relate from one table to the related field in the other table.
To drag multiple fields, press the CTRL key, click each field, and then drag them.

In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table.

The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.
Set the relationship options if necessary.

Click the Create button to create the relationship.



I'm still having a problem though. When I create a new record in CLIENT table (and therefore a new primary autonumber in the primary key, the ClientID field), there is no related record of field created in the INCOME table's ClientID field.