If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Joing tables using Multiple joins
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 |
#2
|
|||
|
|||
Joing tables using Multiple joins
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 "Mark909" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|