Join 2 tables, but need all records

[FeS2]

I have 2 tables in Access 2003. 1 has 3500 records the other 5600. They only have 2 fields in common, Phone number and City but each contain 20 fields. I have an Inner Join that combines the records that have the same Phone numbers in both tables, but now I also need to have all the unigue records shown as well. And as a side note, all this needs to be exported to Excel 2003. If this can be done to a single spreadsheet with a total of 9100 records that would be great. I can do the export but the SQL is a bit harder than I thought.

 

[fredericofonseca]

9100 records presumes that you don't have ANY common record between the two files, this unless I am misunderstanding the problem.

As I see it you need
1- Common records on both files
2- Records on file a and NOT on b
3- records on file B and NOT on a

If this is the case then a select of one file (a), with a "union all" on a select from file (b) where (b) does not exist on (a) will give all you need.

If you really need ALL 9100 records then a union all is enough.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[alvechurchdata]

I have an Inner Join that combines the records that have the same Phone numbers in both tables, but now I also need to have all the unigue records shown as well.

Change the Inner Join to an Outer Join to get all the records which have a common phone number and all those with no match. You'll get Nulls where there is no match and you might have to treat these carefully..

A Union performs a different operation. Something like Customer UNION Employee gives you a new table holding all the records from Customer and all the records from Employee. If there are 1000 customers and 10 employees then you'll get 1010 names in the output.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[FeS2]

From what I can find, Access doesn't support full outer joins. Can you think of another way? I have tried a left outer join witch gives me all the records from 1 table and only the matching from the other table, and I can do a right outer join to do the same thing with the other table. But I can't quite get those 2 queries to work together. Any ideas would be helpful. Thanks everybody.

 

[PHV]

Do an union of the entire left join query with the right join query limited to the missing records.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

You use a left join and you select only records where one of the fields is null.

e.g.

select a.* from tbl1 a
left join tbl2 b
on a.f1 = b.f1
where b.f1 = null


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[FeS2]

I got it done, thanks for everyone's help. What I did is a left outer join query, then a right outer join query. Then a Union All query that exported to Excel. Once I got it to Excel, I just removed duplicates. Again thanks for the help everyone, wouldn't have gotten this far without help.

 

[PHV]

To not send duplicates to excel, do a simple UNION (not UNION ALL).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Once I got it to Excel, I just removed duplicates

If you had used UNION rather than UNION ALL, it would have removed the duplicates for you.