Joing tables using Multiple joins

[Mark909]

I've got two seperate tables with different information.

However they both contain the same address information but the addresses
have different headings.

How do I join the two tables together using multiple joins.

i.e how do i match:

Primary Building Number : Primary Building Number
Secondary Building Number: Secondary Building Number
Street Name : Street Name
Post Code : Post Code

One of the tables also has significantly more addresses than the other
table. I only need to extract data from the bigger table to match the smaller
table.

Thanks for any help

 

[Ken Sheridan]

Its just a question of joining them on all four columns. If you are doing
this in design view simply add both tables to the query and drag from Primary
Building Number in one to Primary Building Number in the other, Secondary
Building Number in one to Secondary Building Number in the other and so on so
that you end up with 4 join lines.

In SQL it would be:

[Table1] INNER JOIN [Table2]
ON [Table1].[Primary Building Number] = [Table2].[Primary Building Number]
AND [Table1].[Secondary Building Number] = [Table2].[Secondary Building
Number]
AND [Table1].[Street Name] = [Table2].[Street Name]
AND [Table1].[Post Code] = [Table2].[Post Code]

The INNER JOIN will return only rows where there is a match in each table
which is what you want. If you ever did want all the rows from the bigger
table along with those which match from the smaller table change the join
type to a LEFT JOIN where Table1 is the bigger table in the above example, of
select the appropriate join type in design view via the Join properties
dialogue. In this case the columns from Table2 where there is no match will
be Null.

However, redundantly storing data in two tables is a bad idea as I'm sure
you realise, and leaves the database wide open to inconsistent data. I
wonder whether you may in fact be doing this with a view to correcting this
serious design flaw? What you should have of course is one table whose
primary key can be referenced by a foreign key in other tables, thus storing
each non-key value just once and eliminating the redundancies.

One thing puzzles me. You said "the addresses have different headings", but
the column headings you then give match (???).

Ken Sheridan
Stafford, England