Ms-access – Make custom query from two table access

ms accessms-access-2013

I have two table that table 1 contains Number, code, ID and table 2 contains Name, Family name, Number, ID, code, Phone number,…

Now I want to make a query that contains table 2 fields but the query should check that if for example table2 contains table1.Number, show the table 2 fields that contains Table1.Number and if table2 doesn't contains the Table1.Number, show the table 2 fields but just fill the fields that table1 and table 2 have them and just for example put "-" in the filed Phone number because table1 doesn't has that but it has code field and table 2 has it too so it should be fill it with the table 1 parameter
And this proccess should be continue for all of the codes of table 1

For example:

Table1 has this data

Table1

Table 2 data

Table2

Query example:

Result

Can someone help me with this please?

Best Answer

  1. Open the query designer for a new query.
  2. Add the tables you are interested in.
  3. Drag the field number from table1 to table2
  4. Drag the field code from table1 to table2
  5. Right click on each of the lines between the two tables and select join properties
  6. Select option 2

enter image description here

  1. Lastly in the bottom section of the designer select the columns you wish to see in your output

enter image description here

Related Question