Relationships in three tables

[hdgirl]

Hi

i am having difficulties setting relationships with three tables. We will have one complaint with many customers and that customer may have many actions against it.

tblComplaint
RefNo Autonumber / Primary Key
ShortDescription
FullDescription

tblCustomer

RefNo
CustomerID Autonumber / Primary Key
FirstName
LastName

tblAction
ActionID Autonumber / Primary Key
CustomerID

i have set the relationships as tblComplaint RefNo to tblCustomer RefNo (one to many) & tblCustomer CustomerID to tblAction CustomerID (one to many).

Somehow when i then create a form based on the three forms the fields just show up as name# and wont allow me to input any data.

can anyone advise wher i am going wrong

Thx


CJB

 

[HitechUser]

This may help some. Microsoft

 

[willir]

hdgirl

First, the #Name error indicates something Access does not understand. Usually, a spelling mistake.

Second, it is difficult to be able to update three tables at the same time. The reason is that with relationships, you have to create the parent record before creating the child. Moreover, HitechUser pointed you to a document on Relationships. There are one-to-many (most common), many-to-many (also common) and one-to-one (not very common) -- you seem to understand this already. Access will want to be able to update one side of the relationship or the other side of the relationship at one time, but not both sides at the same time.

A more typical approach is to use a main form and subforms. Or a main form with combo or list boxes pointing to the related tables.

So you have...
tblComplaint
RefNo (pk)

tblCustomer
CustomerID (pk)
RefNo (fk)

tblAction
ActionID (pk)
CustomerID (fk)

One complaint can have many customers.
One customer can have many actions.

So one approach would be to build a form frmComplaint based on tblComplaint. Add to this / embed a subform sbfrmCustomer based on tblCustomer. FIRST, make sure you have defined your relationship properly. (Fromt the menu, "Tools" -> "Relationships", add your tables, click and drag the primary key to the foreign key, enforce referential integrity.) Use the form wizards to create these forms. By ensuring the relationships are created, Access will properly link the form / subform.

If you want to have a frmCustomer Customer as the main form, then change the RefNo text box to a combo box, and use the query builder to point to tblComplaint. (How to... Form open in design mode. Properties window open -- "View" -> "Properties". Select the RefNo, and "Format" -> "Change to" -> "Combo box". Select "Data" tab in Properties window and select "RowSource". Click on "..." command button to open Query Builder and add tblComplaint, and add columns RefNo and ShortDescription. BoundColumn = 1. Click on "Format" tab, and change ColumnCount = 2, ColumnWidth = 0";1")

Good design ... but ... Now a question to ask you:
How do you decide which action for a customer applies to which complaint? You may want to add RefNo to tblAction and treat this relationship as a many-to-many. A customer can have many complaints. A complaint can be shared by many customers. The tblAction becomes the "joiner" table.

Richard