how to: Build relationship against a defined Primary Key w/ 2 columns 1

[zyrag]

I want to build a master/detail table for my transaction. The transactions i'm referring to are the Check Vouchers, Journal Vouchers, etc. Based on their existing structure, there can be the same transaction number for each type of transaction, say CV-00001 and can also have JV-00001. So i design the master table as follow:

MASTER TABLE
TableName: tblTransactions
Fields: Trans_Type, Trans_Num, Trans_Date
Primary Key: Name=priTransTypeNum, Columns=Trans_Type & Trans_Num, Unique=YEs, Ignore Nulls = No

DETAILS TABLE
TableName: tblTransDetails
Fields: AutoNum, Trans_Type, Trans_Num,EmpID, ReasonCode, Debit, Credit
Primary Key: Name=AutoNum
Indexes: Name=idxTransTypeNum Columns=Trans_Type & Trans_Num

When i tried to build the relationship, I noticed that the relationship type is not 'One-To-Many' but rather 'Indeterminate'.

How do i designed a master/detail table and build a One-To-Many relationship for the master and details table with a PrimaryKey containing 2 columns? Is this possible?

 

[zyrag]

as a remedy, I added an AutoNum column into the Master table and set it as the Primary Key and change the priTransTypeNum key from being the PrimaryKey into an index key with its UNIQUE propery set to YES. The new primary key is then linked into the Details table foreign key column having the UNIQUE property set to NO.

It works fine but if you have a better suggestion please let me know. I'm looking for the best solution for this problem.

Question: Is it possible or advisable to set a Primary Key with more that one column? What is its effect?

 

[Katsy]

Master Table s/have fields:
ID_NUM (Autonumber and Primary Key)
TRANS_TYPE
TRANS_NUM
TRANS_DATE

Set PK on the ID_NUM and then set unique index on the other two fields (table design view, VIEW-->INDEXES).

Child Table s/have fields:
ID_NUM (join)
EMP_ID
REASON_CODE
DEBIT
CREDIT

This is assuming the ID_NUM needs to be repeated in the child table, e.g., detail list.

Set the ID_NUM join in Relationships GUI to have Referential Integrity and cascade all changes.

Pull all together in a SELECT query.

 

[zyrag]

thanks katsy, the ideas we have are basically the same but you reply reminded me to remove the trans_type and trans_num fields in the detail table to avoid update anomalies due to repeated data from two tables. For that, you got a star!