Data Matching two table

[Cass]

I have two tables. now i need check this two table entries
Earlier using three query (1 make table new column (col1) with value 1 and second append but col1 value is 2. then crosstab query separate the data in col1)
Is it possible to resolve this problem with 1 query?
I need check both table values and therefore need all data both tables.
If table1 missing value but table2 is value and contrariwise http://vbaexpress.com/forum/images/smilies/think.gif

[matthewspatrick]

Cass,

If I understand you correctly, you are asking for a query that returns all rows from both tables, in standard SQL parlance a FULL OUTER JOIN. Access's Jet SQL does not support FULL OUTER JOIN, but you can emulate it by using UNION. Assuming table1 and table2 have the same fields in the same order, and both use field col1 as the primary key:


SELECT table1.* FROM table1
UNION
SELECT table2.* FROM table2

Enter that in the SQL pane.

Patrick

[Cass]

Ok as i understand the union join both table records.
Maybe if i draw some image is better overview what i want to do

http://cass.msn.ee/data/qery.jpg
now if neither table is difference then show record and both table volumes. (table1 value and table2 value)

[Cass]

SELECT table1.* FROM table1
UNION
SELECT table2.* FROM table2

Enter that in the SQL pane.

Patrick


OK maybe is possible to add new column when creating union
table1 !column value add X
and
table2!column value add Y

and then using crosstab query separate X and Y value as i want