Table with two customer IDs that I want to relate to the entity

Question

Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2.

I have a list of transaction IDs, eg transaction1 is Party1 sells goods to Party2, transaction2 is Party3 sells goods to Party4 and so on.

I have another table, Entities, that tells me that Party1 is 'Smith Enterprises' and Party2 is 'Jones Corp' etc.

I want to create a query where Access puts the name of the party in for both parties, eg the transaction now says 'Smith Enterprises' sells goods to 'Jones Corp'.

I can't figure out how to get a query to look up Party1 and Party2 in Entities and insert the entity name for both. I can do them one at a time and create a list of identified sellers or a list of identified buyers, then join them back together, but I feel sure there must be a more efficient way of doing this.

Apologies if this is either a trivial request or unclear; I'm a 'semi-competent amateur' trying to help myself in the absence of organisational coding resources. But if you tell me which command(s) I need to use, I can probably figure it out from tutorials.

Answer 1

You want to get names matched to two separate fields.

Fields are indepentent.

So the tables which are used to find relative names must be independent too.

So you need 2 separate copies of names table:

SELECT tr.*, 
       n1.name AS buyer_name, 
       n2.name AS seller_name 
FROM transactions AS tr 
JOIN names AS n1 ON tr.buyer = n1.id 
JOIN names AS n2 ON tr.seller = n2.id

Answer 2

I don't have access to Access (pardon the pun! :-) ), but this should get you pretty close to what you want (see fiddle here):

Create and populate a party table (could also be called entity):

CREATE TABLE party (party_id SERIAL NOT NULL PRIMARY KEY, party_name VARCHAR (25));

INSERT INTO party (party_name) VALUES ('Bill'), ('Fred'), ('Mary'), ('Jim');

Then do the same for a trxn table (don't want to call a table "transaction" - SQL keyword!) So:

CREATE TABLE trxn 
(
  t_id SERIAL NOT NULL PRIMARY KEY, 
  party_1 INT NOT NULL, 
  party_2 INT NOT NULL,
  item VARCHAR (25) NOT NULL,
  t_amount INT NOT NULL CHECK (t_amount > 0), 

  CONSTRAINT ck_parties_different CHECK (party_1 != party_2),
  CONSTRAINT fk_trxn_party_1 FOREIGN KEY (party_1) REFERENCES party (party_id),
  CONSTRAINT fk_trxn_party_2 FOREIGN KEY (party_2) REFERENCES party (party_id)
);

and populate it:

INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (1, 2, 'Car', 45);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (2, 3, 'Car', 50);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (2, 4, 'Ship', 450);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (4, 1, 'Ship', 500);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (3, 2, 'Book', 5);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (2, 4, 'Book', 7);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (3, 1, 'TV', 60);
INSERT INTO trxn (party_1, party_2, item, t_amount) VALUES (1, 2, 'TV', 70);

Obviously, party_1 could be called seller and party_2 the buyer, but I tried to stick to the vocabulary you used in the question.

Then run the following SQL:

SELECT 
  t.t_id, p1.party_id, p1.party_name, ' sold to ' AS Action , p2.party_name, 
  ' a ' AS what, t.item, t.t_amount
FROM party p1
JOIN trxn t
  ON p1.party_id = t.party_1
JOIN party p2
  ON t.party_2 = p2.party_id
ORDER BY t.t_id;

Result:

t_id    party_id    party_name    action    party_name   a  item    t_amount
   1           1          Bill   sold to          Fred   a   Car          45
   2           2          Fred   sold to          Mary   a   Car          50
   3           2          Fred   sold to           Jim   a  Ship         450
   4           4           Jim   sold to          Bill   a  Ship         500
   5           3          Mary   sold to          Fred   a  Book           5
   6           2          Fred   sold to           Jim   a  Book           7
   7           3          Mary   sold to          Bill   a    TV          60
   8           1          Bill   sold to          Fred   a    TV          70

I hope this helps you with your problem. You should probably read up a bit about SQL and in particular about JOINing tables - and especially about using the same table twice within a query which is what you need here - you have to include the party table twice because there is both seller and buyer - (party_1 and party_2). If this is not what you require, leave a comment with @Vérace in the text and I'll see it come up in my inbox. p.s. welcome to the forum! :-)

p.p.s. Almost forgot to mention that if can have multiple transactions between the same seller and buyer, you will have to put a timestamp on your transactions and JOIN using that as well!