Microsoft Access: Related Table and Self/Outer Join

0
votes

I'm using Microsoft access and I've been told to do a self join. So in a self join, I used a table and a copy of that table to form a relationship. Table1 has the primary key and Table2(The copy of table1) has the foreign key.

Question 1: So does that mean Table1 is the primary table and Table2 is the related table?

Then I've been told to "use an outer join on the related table" So I click on the "join properties" and I'm given 2 outer join options.

  1. Include all record from Table1 and only those record from Table2 where the joined fields are equal
  2. Include all record from Table2 and only those record from Table1 where the joined fields are equal

Question 2: If my guess is correct and that Table2 is the related table, would it be the 1st option?

1
ms-access
Homework is for yourself to work with. But a hint: self means to itself. So, delete your Table2. – Gustav
Table2 is just the copy of table1 – Leo Evans
But you were asked for a self join. The wording is both precise and clear. – Gustav
Think foreign key and primary key. That will make everything clear. – nicomp
@LeoEvans The table is not copied. table2 is the same table as table1 – nicomp

1 Answers

0
votes

First do not make a copy of Table1. Doing so, you have two completely different tables, therefore you cannot join to itself. Go to the Query Builder and add Table1 to the grid twice so you have Table1 and Table1_1. So now you have the same table in there twice.

Next connect the Table1 to Table1_1 via the primary key.

As far as left or right outer join, that depends. If you drag the PK from Table1 to the PK of Table1_1 then Table1 is the left table (left join). This means you will get all records from Table1 and only those records from Table1_1 that matches those records in Table1.