Primary Key Issue

[bd528]

Hi,

I have 3 tables, tblQuotes, tblCommission and tblNotes.

Each quote can have multiple notes. In tblNotes I have a Quote_ID, which I have related to QuoteID (PK) in tblQuotes.

Each quote needs to have 6 commissions. I'm not sure how to relate this as I've already used tblQuotes' PK for tblNotes.

Can anyone advise on how to achieve this.

Many thanks

 

[CJ_London]

Each table has a primary key (usually an autonumber) to uniquely identify the record

They may also hold a Foreign (or Family) key which is also indexed and allows duplicates to link back to the primary key of another table

So the minimum requirement in tables to represent relationships is:

tblQuotes
QuotePK autonumber

tblCommissions
CommissionPK autonumber
QuoteFK long (links to quotePK in tblQuotes)

tblNotes
NotesPK autonumber
QuoteFK long (links to quotePK in tblQuotes)

to each of these you would add relevant fields, e.g.

tblQuotes
QuotePK autonumber
Customer text
QuoteDate date

tblCommissions
CommissionPK autonumber
QuoteFK long
Amount currency
PayTo text
PayOn date

tblNotes
NotesPK autonumber
QuoteFK long
Note memo
Madeby text
NoteDate date

 

[bd528]

Each table has a primary key <snip>

Yesterday, this is exactly what I planned to do. I setup the tables exactly as you explained, but when I tried to setup the relationship, I got a warning - I can't remember what though. Now when I try after you post - it works fine! I think previously I may have been trying to create the relationship to<->from the wrong tables.

Thank you for explaining so clearly though. Really appreciated.