0

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.

0

2 Answers 2

0

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

enter image description here

0

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!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.