0

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?

2
  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

1

The query would look something like this:

SELECT
    [Table1].[Code],
    [Table1].[Number],
    [Table2].[Name1],
    [Table2].[Family]
    ...
FROM [Table1]
    LEFT OUTER JOIN [Table2]
ON [Table1].[Code] = [Table2].[Code]
    AND [Table1].[Number] = [Table2].[Number]

Being that this is Access, you'll have to check the documentation for the IIF statement, for replacing NULL values with a -.

However, once you create this query, you should then be able to edit it in the designer.

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.