combine two tables

[boomkrekel]

Hello,

I think I have a very simple question, but I cannot find the solution myself..

I have two tables with sightings of bird species.
Table 1 and Table 2.

Table 1: my species;
Table 2: John's species.

Table 1 has the following fields: Id, species, number;
Tabel 2 only has two fields: species, number.

The relation between the two tables is "species".
Some records are the same, some differ.

I want one table with the following five fields:
Id, species (from table 1), number (1), species (from table 2), number (2).

If I use a QUERY for this, the result is a table with only species that are in both tables.
What I want is a table with ALL species, also the ones that are only in table 1 AND the ones that are in table 2.

I sounds so very basic, however, I cannot find out how to do it...

Can someone help me?



Thanks!

[Rainlover]

I would add,(Append) all the records from tbl2 into tbl1 then delete Table2.

[boomkrekel]

Thanks for your reply,

I would add,(Append) all the records from tbl2 into tbl1 then delete Table2.

But then the result is a table with doubles in the "species" field.
I want a table without doubles and without missing values.

Cheers

[Rainlover]

In Query Design View.

Add both tables.

Next add to the grid the fields from tbl2 that you wish to append plus Species from tbl1.

The Join should be for All Records from tbl2 and omly those records from tbl1 that match.

In your criteria for Species in tbl1 put Is Null.

This should append the records as you described.

I don't know what to do about missing information. If it is not there there is nothing that can be done except to manually add it.

BACK UP before doing this.

[pbaldy]

I suspect what you want is called a FULL OUTER JOIN, which unfortunately is not supported in Access (at least not in previous versions). The workaround in Access is to create 3 queries and UNION them together. The 3 would join your existing tables 3 different ways, with LEFT, RIGHT and INNER joins.

[boomkrekel]

Thanks everybody for your help!
I guess it is not as basic as I thought it would be.

I found a thread which describes what I want:

https://www.accessforums.net/access/...uch-18486.html

Thanks again.