Unmatch query

[user]

Hello

I have table1 with (field Number) with 200 rows of data, and another
table2 with (field Number) with 300 rows of data, including the 200 rows from
table1. I want to create a table that includes all 200 rows from table1 and
include the additional 100 rows from table2. i was trying to use the unmatch
query wizard, but for some reason some fields are missing like the Number
field. Not sure why.

Please help.

 

[John Spencer]

SQL statement to see all records in table 2 and any records in table 3 that
match. This shows ALL fields in both tables in one row.

SELECT *
FROM Table2 LEFT JOIN Table1
ON Table2.[FieldNumber] = Table1.[FieldNumber]

In the query design view
== add both tables
== drag from fieldnumber to fieldnumber to set up a join
== double-click on the join line and in the dialog
== select the option that says ALL fields in table2 and matching in table1
== add the fields you want to see to the list of fields

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[user]

Thanks John for your response, but maybe I was too vague.
In table1, I have Field Number with 200 rows of data, 1 to 200.
In table2, I have Field Number with 300 rows of data, 1 to 300. So, in
table 2, any data with 1 to 200, I don't want that data. From table2, I only
want data 200-300. And, from table1, I want data 1-200.
So, I wanted to create an unmatched query to include all data from table1
and only unmatched data from table2. How do I do this?

Hope this clarifies it.
--
Thanks

SQL statement to see all records in table 2 and any records in table 3 that
match. This shows ALL fields in both tables in one row.

SELECT *
FROM Table2 LEFT JOIN Table1
ON Table2.[FieldNumber] = Table1.[FieldNumber]

In the query design view
== add both tables
== drag from fieldnumber to fieldnumber to set up a join
== double-click on the join line and in the dialog
== select the option that says ALL fields in table2 and matching in table1
== add the fields you want to see to the list of fields

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello

I have table1 with (field Number) with 200 rows of data, and another
table2 with (field Number) with 300 rows of data, including the 200 rows from
table1. I want to create a table that includes all 200 rows from table1 and
include the additional 100 rows from table2. i was trying to use the unmatch
query wizard, but for some reason some fields are missing like the Number
field. Not sure why.

Please help.

.

 

[KARL DEWEY]

That is not an unmatched query. An unmatched query shows what ain't there.

You need a unoin query like this --
SELECT Table1.*
FROM Table1
UNION SELECT Table2.*
FROM Table2
WHERE [Field Number] Between 201 AND 300;

--
Build a little, test a little.

Thanks John for your response, but maybe I was too vague.
In table1, I have Field Number with 200 rows of data, 1 to 200.
In table2, I have Field Number with 300 rows of data, 1 to 300. So, in
table 2, any data with 1 to 200, I don't want that data. From table2, I only
want data 200-300. And, from table1, I want data 1-200.
So, I wanted to create an unmatched query to include all data from table1
and only unmatched data from table2. How do I do this?

Hope this clarifies it.
--
Thanks

SQL statement to see all records in table 2 and any records in table 3 that
match. This shows ALL fields in both tables in one row.

SELECT *
FROM Table2 LEFT JOIN Table1
ON Table2.[FieldNumber] = Table1.[FieldNumber]

In the query design view
== add both tables
== drag from fieldnumber to fieldnumber to set up a join
== double-click on the join line and in the dialog
== select the option that says ALL fields in table2 and matching in table1
== add the fields you want to see to the list of fields

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello

I have table1 with (field Number) with 200 rows of data, and another
table2 with (field Number) with 300 rows of data, including the 200 rows from
table1. I want to create a table that includes all 200 rows from table1 and
include the additional 100 rows from table2. i was trying to use the unmatch
query wizard, but for some reason some fields are missing like the Number
field. Not sure why.

Please help.

.

 

[John Spencer]

It kind of depends on if the two tables have the same structure or not.

If table1 and Table 2 have the same structure then you can use a UNION query
to return the two sets of records. (Or if you can match the fields up between
the two tables for all the fields you want to see, you can use a union query)

If the two tables have different structures, then you have a problem. Since a
query returns a set number of columns and cannot vary that set from row to row.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[user]

Karl

The number is not necc. 201- 300 so that where clause wouldn't work.
The numbers are random, and not sequential.
How do i change the where clause, WHERE [Field Number] Does Not Equal
[Field Number] in table1.

Appreciate the help.


--
Thanks

That is not an unmatched query. An unmatched query shows what ain't there.

You need a unoin query like this --
SELECT Table1.*
FROM Table1
UNION SELECT Table2.*
FROM Table2
WHERE [Field Number] Between 201 AND 300;

--
Build a little, test a little.

Thanks John for your response, but maybe I was too vague.
In table1, I have Field Number with 200 rows of data, 1 to 200.
In table2, I have Field Number with 300 rows of data, 1 to 300. So, in
table 2, any data with 1 to 200, I don't want that data. From table2, I only
want data 200-300. And, from table1, I want data 1-200.
So, I wanted to create an unmatched query to include all data from table1
and only unmatched data from table2. How do I do this?

Hope this clarifies it.
--
Thanks

SQL statement to see all records in table 2 and any records in table 3 that
match. This shows ALL fields in both tables in one row.

SELECT *
FROM Table2 LEFT JOIN Table1
ON Table2.[FieldNumber] = Table1.[FieldNumber]

In the query design view
== add both tables
== drag from fieldnumber to fieldnumber to set up a join
== double-click on the join line and in the dialog
== select the option that says ALL fields in table2 and matching in table1
== add the fields you want to see to the list of fields

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

user wrote:
Hello

I have table1 with (field Number) with 200 rows of data, and another
table2 with (field Number) with 300 rows of data, including the 200 rows from
table1. I want to create a table that includes all 200 rows from table1 and
include the additional 100 rows from table2. i was trying to use the unmatch
query wizard, but for some reason some fields are missing like the Number
field. Not sure why.

Please help.

.

 

[user]

The structures are not the same. So, I will need to figure a different route.
I was thinking of appending. I want all data from Table1, which has many
additional fields, that are not in Table2. My problem if I append table 2
which has all the data, to table 1, how would I exclude the the Number field,
which is unique, from being duplicated.

 

[John Spencer]

IF you have a UNIQUE index on the NumberField in table1, you can append
records from table2 and any where the numberfield is already in table1 will
not be added.

Or you can build a query to find UNMATCHED records in table2 and then use that
as the source for the append query.

The SQL statement you might end up with would look something like:

INSERT INTO Table1
SELECT Table2.*
FROM Table2 LEFT JOIN Table1
ON Table2.NumberField = Table1.NumberField
WHERE Table1.NumberField is Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County