Hi, would appreciate any guidance on the following before I make it too complicated of a solution for a simple issue.

I have two tables (a) and (b) indicated below

(a) Company
Column 1 = primary key = ID_Ticker (text)
Column 2 = Company (text)
Column 3 = Headquarters (text)

(b) Transaction
Column 1 = primary key = ID_Transaction (auto)
Column 2 = Description (text)
Column 3 = ID_BuyerCompany (this foreign key ID_Ticker from table (a))
Column 4 = ID_SellerCompany (same foreign key ID_Ticker from table (a) as column 3)

So in table (b) column 3 and 4 are the same foreign key but the idea is that both the buyer and seller company can come from the list of companies which is in table (a)

Now for example if I run a query
Column 1 = (b) ID_Transaction
Column 2 = (b) Description
Column 3 = (b) ID_BuyerCompany
Column 4 = (a) HQ -> I want this to show for the ID_BuyerCompany
Column 5 = (b) ID_SellerCompany
Column 6 = (a) HQ -> I want this to show for the ID_SellerCompany

The problem is for Column 4 and 6 ... right now I know it only shows the field chosen as "HQ" from table (a) Company ... and therefore will come back blank in the query since it doesn't know whether to look at ID_BuyerCompany or ID_SellerCompany

To fix Column 4 and Column 6 to produce the correct results...instead of having table (b) Transaction Column 3 and 4 BOTH as the foreign key to the same ID_Ticker in (a) Company table... would I need to create another table (c) Buyer and (d) Seller that each link to the (a) Company table ID_Ticker and then have the foreign key for (c) and (d) linked in columns 3 and 4 in table (b)?

Please tell me if this is confusing. Thank you.